deleting empty rows

[kaipan]

Hi,

I need to write a macro which checks cells in one column and if the cell is empty it deletes the whole row (which contains the cell).

I tried this code but it doesn't delete all rows with empty cells:

Public Sub deleterows()
For o = 7 To 25
     If Worksheets("sheet").Cells(o, 4) = "" Then
        Rows(o).Delete
        End If
     End If
Next o
End Sub

So I modified the code:

Public Sub deleterows()
For o = 7 To 25
     If Worksheets("sheet").Cells(o, 4) = "" Then
        Rows(o).Delete
         o = o - 1
         a = a + 1
         If a = 20 Then
             o = 25
         End If
     End If
Next o
End Sub

It works correct but it is not a proper solution (it ends the for loop after 20 deleted cells)...

Do you have any better suggestions?

Thanks!

[shg]

You need to iterate from bottom to top when you're deleting rows. Think about it ...

[broro183]

hi Kaipan,

Welcome to the Forum
... have you tried searching for a solution before posting your question?

The macro that you have shown is designed to only check the rows between row 7 & row 25 as stated by the initial For statement & because of this, it appears it was written for a very specific purpose. Your modified attempt does recognise that once a row is deleted, the counter becomes inaccurate if the macro runs from the top to the bottom of the data, because of this it is wise to process from the bottom up (eg http://j-walk.com/ss/excel/tips/tip56.htm).

If the above link isn't the best approach for you, "Delete row macros" are commonly asked about & I think you'll find a solution with a quick glance at some of the links on the below search:
http://www.google.co.uk/#hl=en&sourc...14e9eaa343dff4


hth
Rob

[Shijesh Kumar]

Sub x()
    For r = 75 To 7 Step -1
        If Cells(r, 7) = "" Then
            Cells(r, 7).EntireRow.Select
            Selection.Delete
            
        End If
    Next r
End Sub

[DonkeyOte]

...or if the cells are true blanks (not formula nulls) then (avoiding iteration):

Sub x
On Error Resume Next
Range(Cells(7,4),Cells(25,4)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

[kaipan]

thank you for answers!

next time I will first search for a solution on forum...

[broro183]

Thanks for the feedback & marking the thread as solved - I'm pleased we could help :-)

Rob