macro to delete blank rows? Solved/Closed

Question

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.

Configuration: Windows XP / Internet Explorer 7.0

rizvisa1 · Accepted Answer

'******** 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

rizvisa1 · Answer

paste your recoded macro

ap · Answer

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

Macro to delete blank rows?

3 responses

Similar discussions

Subscribe To Our Newsletter!