Select each nth row in Excel

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

Todd Pierzina · Accepted Answer · 2009-11-12T20:15:04.150

21

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

edited Nov 12 '09 at 20:15

answered Nov 11 '09 at 22:27

Todd Pierzina

581
4
9

Not a bad solution. I like my macros so that's my first choice, but this could also work with a bit of effort. – Nov 11 '09 at 22:30
2

=MOD(ROW(),7) you need that second argument. – dkusleika Nov 11 '09 at 22:48

score 3 · Answer 2 · edited Aug 16 '14 at 16:23

3

Insert a column.
In the first row there, insert 1.
Copy down to row 7 with Ctrl depressed.
Grab that block and do the same again to the end.
Data/Filter the ones you don’t want and delete these.
Delete the inserted column.

edited Aug 16 '14 at 16:23

Peter Mortensen

12,090
23
70
90

answered Nov 08 '12 at 12:23

pnuts

6,072
3
28
41

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 08 '12 at 14:43

score 3 · Answer 3 · answered Nov 11 '09 at 22:25

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

answered Nov 11 '09 at 22:25

If you do use a macro, try going backward

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

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 00:06
1

He says "by select, I mean delete all the other rows..." – dkusleika Nov 12 '09 at 15:58

score 3 · Answer 4 · edited Nov 15 '12 at 16:21

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

score 1 · Answer 5 · answered Jul 17 '17 at 05:16

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!

score 0 · Answer 6 · answered Oct 25 '18 at 16:21

0

for every 7th row,

Insert a column
In row 1 to row 6 put an "X"
in row 7 put 1,
auto-fill your column with that block
Use "Remove Duplicates" on that column
Delete 1st row (with the 1st "X").
Delete added column

answered Oct 25 '18 at 16:21

Ponch

1

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 09 '18 at 13:20

score 0 · Answer 7 · answered Oct 03 '21 at 00:42

In case each row only has 1 or a few columns, one can use this formula from https://exceljet.net/formula/copy-value-from-every-nth-row (mirror):

=OFFSET($B$5,(ROW(A1)*n)-1,0)`

Demo with =OFFSET($B$5,(ROW(D1)*3)-1,0) (= selecting every 3 elements of column B starting at B5 into column D starting at D5):

Select each nth row in Excel

7 Answers7

Linked