Excel macro to delete empty rows isn't stopping
Posted
by
RocketGoal
on Super User
See other posts from Super User
or by RocketGoal
Published on 2013-06-25T10:06:39Z
Indexed on
2013/06/25
10:23 UTC
Read the original article
Hit count: 148
How do I make it stop after a certain amount of rows?
I've taken a VBA course and my teacher explained how to delete empty rows. I'm now trying to put this in place but my macro isn't stopping. I thought I had limited it to 200 rows.
I'm missing something important. Any pointers much appreciated.
Sub RemoveRows()
' Remove rows from last blank cell
Dim LastRow As Long
Dim ISEmpty As Long
'Count how many records in the list. This is done so that the Do loop has a finish point.
LastRow = Range("A200").End(xlUp).Row
'Start at the top of the list
Range("A1").Select
'Loop until the end of the list
Do While ActiveCell.Row < LastRow
'Assign number of non empty cells in the row
ISEmpty = Application.CountA(ActiveCell.EntireRow)
'If ISEmpty = 0 then delete the row, if not move down a cell into the next row
If ISEmpty = 0 Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
© Super User or respective owner