Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 12 down vote accepted
  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
share|improve this answer
Not a bad solution. I like my macros so that's my first choice, but this could also work with a bit of effort. – user3463 Nov 11 '09 at 22:30
=MOD(ROW(),7) you need that second argument. – dkusleika Nov 11 '09 at 22:48
+1 Good answer. I prefer a no macro solution when it is possible. – DaveParillo Nov 12 '09 at 0:43
This answer suffers from the "single line-feed doesn't do anything" feature of Markdown. Can anyone format it properly? – djeidot Nov 12 '09 at 14:54
Okay, I fixed it (and included the missing ,7 from MOD. Sorry! – Todd Pierzina Nov 12 '09 at 20:15

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

For the record, I don't think you could ever come up with this by recording. Macro recording is a good way to familiarize yourself with the Excel Object Model, but not a 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")
        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
        MsgBox "The row multiple provided must be greater than 0"
    End If

    Set keepRows = Nothing
End Function
share|improve this answer
With you on the reusable notion. – user3463 Nov 12 '09 at 7:11
  1. Insert a column.
  2. In the first row there, insert 1.
  3. Copy down to row 7 with Ctrl depressed.
  4. Grab that block and do the same again to the end.
  5. Data/Filter the ones you don’t want and delete these.
  6. Delete the inserted column.
share|improve this answer
Thanks for the answer. Can you elaborate on step 3? Do you mean to hold down Ctrl while dragging the mouse? I don't have access to Excel 2003 at the moment so I can't test. – Patrick McElhaney Nov 8 '12 at 14:43
@PatrickMcElhaney Sorry, I could have been clearer! Grab cell containing the keyed '1', drag down to Row 7 while depressing left mouse button and hold down Ctrl before releasing mouse button. Just dragging down should display a small 1 SE of the cursor and this should change to 7 while Ctrl in pressed. Re 2003, I don't either but from memory Excel 2007 is no different in this respect (I hope!) – pnuts Nov 8 '12 at 14:52

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
share|improve this answer
If you do use a macro, try going backward <code><pre>Sub delrows() Dim i As Long For i = 988 To 1 Step -7 Sheet1.Cells(i, 1).Offset(1, 0).Resize(6).EntireRow.Delete Next i End Sub</pre></code> If you delete rows, it the loop won't go crazy. – dkusleika Nov 11 '09 at 22:52
He's not deleting, just adding a row to a range object (implied in the comment) – DaveParillo Nov 12 '09 at 0:06
He says "by select, I mean delete all the other rows..." – dkusleika Nov 12 '09 at 15:58

Your Answer


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.