15

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

19
  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
| 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
  • 2
    =MOD(ROW(),7) you need that second argument. – dkusleika Nov 11 '09 at 22:48
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
...
| 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
  • 1
    He says "by select, I mean delete all the other rows..." – dkusleika Nov 12 '09 at 15:58
3

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")
    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
| improve this answer | |
  • With you on the reusable notion. – user3463 Nov 12 '09 at 7:11
3
  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.
| 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
1

to select every 7th row there is an EASIER WAY: in the first 7 rows of your column but one (the first) you write something inside. Then you select this 7 rows and copy them on the entire column. Now what you need it is just to go to SELECT->GO TO SPECIAL->select BLANKS->OK You have each 7 rows a selection. Now you can do what you want. Enjoy!

| improve this answer | |
0

for every 7th row,

  1. Insert a column
  2. In row 1 to row 6 put an "X"
  3. in row 7 put 1,
  4. auto-fill your column with that block
  5. Use "Remove Duplicates" on that column
  6. Delete 1st row (with the 1st "X").
  7. Delete added column
| improve this answer | |
  • Welcome to Super User. This is essentially the methods proposed in the accepted answer and the one by pnuts, only more work (and destructive). It isn't clear what it contributes. – fixer1234 Oct 25 '18 at 16:49
  • It's just using another menu, not really more work in my view nor more destructive than the original poster requests ("delete all the other rows"). – Ponch Nov 9 '18 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.