Previous Topic Next Topic

Macro to delete blank rows?

[Solved/Closed]
Report
-
 ap -
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
'******** 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
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Report

Thank you so much - it worked! I appreciate it.
Answer 2 / 3
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
paste your recoded macro
Answer 3 / 3

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
Ask a question