Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Delete empty cells and shift up

  1. #1
    Join Date
    14th December 2004
    Posts
    13

    Delete empty cells and shift up

    Hi.

    Could anyone help me with a vba script that:

    1. Goes through colomn A (a2:a353)
    2. Finds and deletes any empty or empty-looking cells
    3. Shifts-up
    4. do the same for colomn B, C and D (same range as A)


    Nic

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: Delete empty cells and shift up

    Hi,

    Try deleting a cell, moving down and deleting another. Do this whilst recording the macro and you have the bare bones of your code.

    Place this in a for next loop but I would suggest going from i= 353 to 1 step -1

    Use RC references i.e. activesheet.cells(2,4) and you can replace the 2 or the 4 with i or j for doing the five columns.

    You are testing to see if activesheet.cells(2,4).value = ""

    I HTH

    John

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Delete empty cells and shift up

    Far more efficient to use SpecialCells method.

    EG

    VB:
    Option Explicit 
     
    Sub DeleteBlanks() 
        Dim intCol As Integer 
         
        For intCol = 1 To 3 
            Range(Cells(3, intCol), Cells(353, intCol)). _ 
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp 
        Next intCol 
    End Sub 
    
    
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  4. #4
    Join Date
    14th December 2004
    Posts
    13

    Re: Delete empty cells and shift up

    I tried to run that but got a 400 error

    Nic

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    14th December 2004
    Posts
    13

    Re: Delete empty cells and shift up

    Hi John,

    thanks for your reply.

    The thing is: "i'm a simply peasant" and you have lost me.

    I know how cut'n'paste and run and slightly modify a macro, but thats where my expertice ends

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Delete empty cells and shift up

    Quote Originally Posted by WillR
    Far more efficient to use SpecialCells method.

    EG

    VB:
    Option Explicit 
     
    Sub DeleteBlanks() 
        Dim intCol As Integer 
         
        For intCol = 1 To 3 
            Range(Cells(3, intCol), Cells(353, intCol)). _ 
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp 
        Next intCol 
    End Sub 
    
    
    Works fine for me. Having reread your post, change the code to

    VB:
    Option Explicit 
     
    Sub DeleteBlanks() 
        Dim intCol As Integer 
         
        For intCol = 1 To 4 'cols A to D
            Range(Cells(2, intCol), Cells(353, intCol)). _ 
            SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp 
        Next intCol 
    End Sub 
    
    
    If you get an error, please let me know which line it errors on & what the exact error message is.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  7. #7
    Join Date
    14th December 2004
    Posts
    13

    Re: Delete empty cells and shift up

    same error massage.

    Just 400 nothing else.

    Do I have to select the range I want to use the macro on?
    The cells are not quit empty, but filled with a formula - does that matter?

    Nic

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Delete empty cells and shift up

    Quote Originally Posted by vestlink
    same error massage.

    Do I have to select the range I want to use the macro on?
    no

    Quote Originally Posted by vestlink
    The cells are not quit empty, but filled with a formula - does that matter?

    Nic
    Well they are either empty or not As per your original post, my code only deletes empty cells - I hadn't got a clue what you meant by "Empty Looking" If the cells are not empty, you will need to loop through each one in turn, testing for either "emptiness" or formulas that return nothing...

    I still have no idea why you get a '400 error' - ike I said, it works fine on my sample data. Perhaps you could upload a small sample for me to test on.
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

  9. #9
    Join Date
    14th December 2004
    Posts
    13

    Re: Delete empty cells and shift up

    Heres the example i'm working on

    Nic
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678

    Re: Delete empty cells and shift up

    there are no formulas ? I thought you said you had formulas ?
    Kind Regards, Will Riley

    Web Presence:
    LinkedIn: Will Riley

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Possible Answers

  1. Delete Empty Cells With No Data
    By Nobi in forum EXCEL HELP
    Replies: 3
    Last Post: March 27th, 2008, 09:40
  2. Delete Row When All Cells Are Empty
    By Designer_6 in forum EXCEL HELP
    Replies: 7
    Last Post: February 5th, 2008, 09:59
  3. Delete Rows If Cells Are Empty
    By lolo70 in forum EXCEL HELP
    Replies: 3
    Last Post: January 8th, 2008, 05:07
  4. Delete And Shift Cells Based On Cell Values
    By kayel in forum EXCEL HELP
    Replies: 8
    Last Post: May 23rd, 2007, 06:16
  5. Delete Cells Shift Left
    By geonicho in forum EXCEL HELP
    Replies: 11
    Last Post: March 20th, 2007, 21:47

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno