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: 143

Filed under:
|
|

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

Related posts about microsoft-excel

Related posts about macros