This site will look better if you upgrade to a browser that supports web standards.
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
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.