Delete all blank rows macro
Posted by JackJJ on January 31, 2000 1:22 PM
Does anyone have the macro that will allow me to delete all the blank lines in a spreadsheet?. At one time I did have this macro, however I can not find it at this time.
Thanks
JackJJ
Posted by Celia on January 31, 2000 4:07 PM
Jack
The following is one way of deleting blank rows in the selected range. Multiple areas can be selected at one time
Regards
Celia
Sub DeleteBlankRows()
Dim num As Integer, I As Integer
Dim area As Object
For Each area In Selection.Areas
area.Select
num = Selection.Rows.Count
If num = 65536 Then
num = Selection.SpecialCells(xlLastCell).Row
End If
'loop through each row and delete if all cells are empty.
For I = 1 To num
If Application.CountA(ActiveCell.EntireRow) = 0 Then
ActiveCell.EntireRow.Delete
Else
If I <> num Then ActiveCell.Offset(1, 0).Select
End If
Next I
Next 'loop back and do next area
End Sub
