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

23

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

601

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,326

answered Nov 08 '12 at 12:23

pnuts

6,242

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

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

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

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