Deleting rows- code help

[abrown]

In this macro I want it to look at columns A to E and if all of the cells from A to E are blank I want it to delete the entire row. Right now with the code below it is working but it is deleting the lines one at a time. How can I change it to do them all at once?

Sub ColorEmpty()
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
    If IsEmpty(Cells(i, 5)) Then
        Cells(i, 5).EntireRow.Delete
  End If
  Next i
 

End Sub

 

[Jonmo1]

Range("E1:E" & LastRow).SpecialCells(xlcelltypeblanks).EntireRow.Delete

 

[Jonmo1]

Also, just a side note.

When you do need to use a loop to delete rows (or columns), you have to go backwards. And sometimes you really do need to use a loop to do it. Because some criteria can't be tested so easily like blanks.

You have to go backwards from Bottom to top. Because...
Say you started the loop on row 1....
It got to say row 10 and met the criteria. So row 10 was deleted.
Whatever was in Row 11 is now in Row 10, and whatever was in row 12 is now in row 11. But your loop moves on to 11.
so it effectively skipped row 11, because what was originally in row 11, is now in row 10.

Same thing applies for deleting columns, go from right to left.


and to make it go backwards, change
For i = 1 To LastRow
to
For i = LastRow to 1 step -1

 

[Z51]

This code checks each row to see if all 5 cells are empty (A1:E1, A2:E2, etc.) and if so deletes the row. As jonmo said, it goes from bottom up for reasons he noted.

Sub test()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = Range("A65536").End(xlUp).Row To 1 Step -1
        If Application.WorksheetFunction.CountA(Range("A" & i & ":E" & i)) = 0 Then
            Range("A" & i).EntireRow.Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[abrown]

Thanks works great!