17

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 7

22
  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
  • 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, 2009 at 22:30
  • 2
    =MOD(ROW(),7) you need that second argument.
    – dkusleika
    Nov 11, 2009 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
...
3
  • 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, 2009 at 22:52
  • He's not deleting, just adding a row to a range object (implied in the comment) Nov 12, 2009 at 0:06
  • 1
    He says "by select, I mean delete all the other rows..."
    – dkusleika
    Nov 12, 2009 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
1
  • With you on the reusable notion.
    – user3463
    Nov 12, 2009 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.
1
  • 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. Nov 8, 2012 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!

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
2
  • 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, 2018 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, 2018 at 13:20
0

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):

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .