McGimpsey & Associates : Excel : Delete every Nth Row

This site will look better if you upgrade to a browser that supports web standards.

Delete Every Nth Row

Many people initially try to set up a loop to delete every 2nd or 3rd row like this:

    For i = 2 To numRows Step 2
        Rows(i).Delete
    Next i

This can lead to a royal mess, because XL, when you delete row 2, renumbers rows 3, 4 and 5... to 2, 3 and 4... so that the next row to be deleted will be row 4, which used to be row 5. Not what they intended.

One way to fix that is to count backwards:

    For i = numRows To 2 Step -2
        Rows(i).Delete
    Next i

This will work (because the renumbering takes place "below" the next deletion) and is the basis for a lot of good code out in the wild. There is one problem with it that I don't like, however. Rows are deleted one at a time, and the remaining rows are renumbered. If you have a lot of rows, this can take considerable time, especially if you don't turn ScreenUpdating off. I prefer to populate a range representing the rows to be deleted, then delete it all at once. It's faster, and doesn't require the ScreenUpdating code:

    Public Sub DeleteEveryNthRow(Optional N As Long = 2)
        'The default is to delete every 2nd row
        Dim rDelete As Range
        Dim i As Long
        With ActiveSheet
            Set rDelete = .Rows(N)
            For i = 2 * N To .UsedRange.Rows.Count Step N
                Set delRange = Union(rDelete, .Rows(i))
            Next i
        End With
        rDelete.Delete
    End Sub

Of course, there are other ways to do it. This routine autofills the first empty column and deletes the rows all at once.

    Public Sub DeleteEveryNthRowQuickly(Optional N As Long = 2)
        'The default is to delete every 2nd row
        Application.ScreenUpdating = False
        On Error Resume Next
        With ActiveSheet.UsedRange
            With .Columns(.Columns.Count).Offset(, 1).Cells
                .Item(N) = True
                Range(.Item(1), .Item(N)).AutoFill .Cells
                .SpecialCells(xlConstants).EntireRow.Delete
            End With
        End With
        Application.ScreenUpdating = True
    End Sub

This page last updated Sunday, 4 January 2004

McGimpsey & Associates

Home

Macintosh XL Pages

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

Excel VBA Macros

Delete Every Nth Row