Removing Blank Rows?

[andycharger]

Im writing a fiddly macro that seems to have a few problems with blan
rows.

Basically, my code is cycling through the rows in a spreadsheet an
deleting certain rows.

The code im using for this is :
Rows(n).Entirerow.Delete

Great!

Trouble is, i need to go through the rows at the end of the loop an
delete out all of the blank space rows left behind.

Im doing this as follows:


Code
-------------------

Lastrow = Activesheet.UsedRange.Rows - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = Lastrow to 1 step - 1
If Application.CountA(Rows(r)) = 0 then Rows(r).delete
end if
next r

-------------------


Is there a better way of getting rid of the rows rather than looping
Its just my code is taking forever.

Cheer

 

[Francesco Sblendorio]

I'd try this:

---------------------------------------
Lastrow = Activesheet.UsedRange.Rows - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
r = Lastrow
Do While r >= 1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).delete
Else
r = r - 1
Loop

 

[andycharger]

But isnt this doing exactly what my code is doing?

I cannot see how that would speed it up

 

[Jim Cone]

andycharger,

The code will not run the way you posted it.
1. The first line of your code has a syntax error.
Remove the "s" from the end of Rows so it looks like...

LastRow = Activesheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

2. The "End If" should be removed.

'-------------------------------------
Once the code runs, then you can probably speed it up
by finding the last row with data instead of using the last
row of the used range. The last row with data can be
determined by calling the following function like this...

LastRow = GetBottomRow(ActiveSheet)

'=================================================
' GetBottomRow() Function
' Aug 31, 2001 - Created by Jim Cone
' Returns the number of the last worksheet row with data.
' If the sheet is blank it returns 0.
'=================================================
Function GetBottomRow(ByRef TheSheet as Worksheet) As Long
On Error GoTo NoRow
If TheSheet.FilterMode Then TheSheet.ShowAllData
GetBottomRow = TheSheet.Cells.Find(what:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function

'-------------------------------------------------------------

Regards,
Jim Cone
San Francisco, CA