formula to remove blank rows

[Bryan]

is there a formula to remove all blank rows in a sheet all at once

 

[Gary''s Student]

Try this macro:


Sub Macro1()
Dim j As Long
Dim i As Long
Dim r As Range

j = 65536
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
If r Is Nothing Then
Set r = Rows(i)
Else
Set r = Union(r, Rows(i))
End If
End If
Next i

If Not r Is Nothing Then
r.Delete
End If
End Sub