Select each nth row in Excel - Super User

Select each nth row in Excel

2009-11-11T22:10:59Z

I have an Excel spreadsheet with thousands of rows. I want to select every 7th row from that spreadsheet. (By "select," I mean delete all of the other rows, or copy the selected rows into a new worksheet.)

What's the easiest way to do that in Excel 2003?

Answer by Randolph Potter for Select each nth row in Excel

2009-11-11T22:25:47Z

Personally, I'd record a macro to do the first three or four lines (at 7-row increments, of course), and copy the lines to a new sheet. Then I'd edit the macro to use a loop that counts to the number of populated rows in the sheet, with a step of 7.

Pseudo-code example:

Dim i as Integer

For i = 1 To 1000 Step 7
    'Add current row to selection
    ...
Next i

'Copy the selected rows to new sheet
...

Answer by tpierzina for Select each nth row in Excel

2009-11-11T22:27:52Z

Insert a column
In first row insert formula =MOD(ROW(),7)
Copy down
Copy/paste special/values
Data/Filter out the ones you want (0 or 6, probably)
Delete the rest of the rows Remove filter Delete column

Answer by DaveParillo for Select each nth row in Excel

2009-11-12T00:42:01Z

Really just finishing the idea Randolph Potter started....

For the record, I don't thing you could ever come up with this by recording. macro record is a good way to familiarize yourself with the Excel Object Model, but a not very good way to write reusable functions.

Option Explicit

'A simple test that copies every 7th row from the active sheet to a new sheet.
Sub SimpleTest()
    Dim r As Range
    Dim ws As Worksheet

    Set r = GetEveryNthRow(7)
    If Not r Is Nothing Then
        Set ws = Worksheets.Add(Before:=Sheets(1))

        r.Copy ws.Range("A1")
    Else
        MsgBox "Nothing came back from GetEveryNthRow"
    End If
    Set ws = Nothing
    Set r = Nothing
End Sub

'
Function GetEveryNthRow(ByVal NthRow As Long) As Range
    Dim keepRows As Range
    Dim r As Range

    If NthRow > 0 Then
        Set keepRows = Rows(1)
        For Each r In ActiveSheet.UsedRange.Rows
            If (r.Row Mod NthRow) = 0 Then
                Set keepRows = Union(keepRows, Rows(r.Row))
            End If
        Next r
        Set GetEveryNthRow = keepRows
    Else
        MsgBox "The row multiple provided must be greater than 0"
    End If

    Set keepRows = Nothing
End Function