Question

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?

See also: Select each nth row in Numbers

Answer 1

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

Answer 2

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

Answer 3

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
...