Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

'Start at the top of the list

This is the problem. When deleting rows, always start at the end of the list to avoid an endless loop.

Step through the code. It will delete the empty rows above the populated cells, then step by step select the populated cells. After that, it will select the empty cell below the populated cells and delete that row.

If that row is, for example, row 35, then row 35 will be deleted. But the rows below will move one up, so you never really delete row 35. After the delete, the selection still sits in row 35. Hence you have an endless loop.

Build the loop from the bottom up instead.

Option Explicit

Sub RemoveRows()
' Remove rows from last blank cell

Dim LastRow As Long
Dim ISEmpty As Long
Dim i 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
For i = LastRow To 1 Step -1

'Assign number of non empty cells in the row
    ISEmpty = Application.CountA(Range("A" & i).EntireRow)

'If ISEmpty = 0 then delete the row, if not move up a cell into the previous row
        If ISEmpty = 0 Then
            Range("A" & i).EntireRow.Delete
        End If
Next i

End Sub

This can probably be done in a more elegant way, but it hopefully gives you a start.

share|improve this answer
    
Thank you it's done exactly what I needed it to do, and thought mine would. I have a better understanding of where to begin my actions now. –  RocketGoal Jun 25 '13 at 12:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.