Select each nth row in Excel

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

0

7 Answers

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

2

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

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

1

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.

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!

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

2

In case each row only has 1 or a few columns, one can use this formula from (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):