Deleting rows- code help

A

abrown

Board Regular
Joined
Nov 15, 2003
Messages
172
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?

Code: 
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Range("E1:E" & LastRow).SpecialCells(xlcelltypeblanks).EntireRow.Delete
 
Upvote 0
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
 
Upvote 0
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.
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
145
Tupe77
T
L
  • Question
Auto-Archive deleted Rows in Excel
2 3 4
Replies
30
Views
471
liljunga
L
G
  • Solved
Macro not reaching bottom of the worksheet (due to blank rows)
Replies
5
Views
202
Peter_SSs
Peter_SSs
M
  • Question
VBA - Deleting Entire Row Based on a Condition
Replies
2
Views
526
jolivanes
jolivanes
D
  • Solved
Copy row based on cell value and paste as text in another worksheet
Replies
2
Views
524
DH937
D

Forum statistics

Threads
1,215,306
Messages
6,124,160
Members
449,146
Latest member
el_gazar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top