Delete empty rows with macro by John Walkenbach

[Steve G]

I am trying to delete empty rows. I copied this macro from Mcrosoft
Excel 2000 Power Programming with VBA by John Walkenbach. I have
Excel 2003. The code below is identical to what is in the book except
for the two 'Dim' statements. The macro is one of 4 modules in my
personal.xls file. I get the following error message:

Run time error '91.' Object variable or with block variable not set.

Any help would be appreciated. Steve G


Sub DeleteEmptyRows()

Dim LastRow As Range
Dim r As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

 

[Jim Cone]

LastRow should be declared as a Long.
(same for r)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Steve G" <[email protected]>
wrote in message I am trying to delete empty rows. I copied this macro from Mcrosoft
Excel 2000 Power Programming with VBA by John Walkenbach. I have
Excel 2003. The code below is identical to what is in the book except
for the two 'Dim' statements. The macro is one of 4 modules in my
personal.xls file. I get the following error message:

Run time error '91.' Object variable or with block variable not set.
Any help would be appreciated. Steve G

Sub DeleteEmptyRows()
Dim LastRow As Range
Dim r As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

 

[Paul D. Simon]

Perhaps this will also help you. (You do have to highlight a range
first, though.)


Sub DeleteEmptyRows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


P.S.
My apologies if my reply appears twice. I can't seem to get me
replies to post to this Newsgroup anymore.