Previous Topic Next Topic

Macro to delete blank rows?

Solved/Closed
ap - Apr 19, 2010 at 11:13 AM
 ap - Apr 19, 2010 at 12:30 PM
I have a spreadsheet where I would like to 'record new macro' to delete all rows that are blank. If I filter on blank rows and delete....the macro will only delete those exact rows that I select in the 'record'. I would like for it to encompass any blank row....because the blank rows may change.

3 replies

Answer 1 / 3
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 19, 2010 at 12:10 PM
'******** DO THIS BEFORE APPLYING FILTER ***********
lMaxRow = Cells(Rows.Count, "A").End(xlUp).Row
' ********************************************

Cells.Select
Selection.AutoFilter Field:=1, Criteria1:="="
Rows("2:" & lMaxRow).Select
Selection.Delete Shift:=xlUp

Range("B2").Select
2
ap
Apr 19, 2010 at 12:30 PM
Thank you so much - it worked! I appreciate it.
0
Answer 2 / 3
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 19, 2010 at 11:31 AM
paste your recoded macro
0
Answer 3 / 3
ap
Apr 19, 2010 at 11:52 AM
Selection.AutoFilter Field:=1, Criteria1:="="
Rows("15:267").Select
Selection.Delete Shift:=xlUp
Selection.AutoFilter Field:=1
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 234
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
Range("B2").Select
End Sub
0