Copy every nth row into a new worksheet

[Guest]

I have a worksheet that contains 30000 rows of data plus tow header rows. I
want to copy every nth row into a new worksheet.

Any suggestions on how to program this are greatly appreciated.

Hal

 

[Guest]

Here is some code...

Option Explicit

Private Const intOffsetRows As Integer = 3

Public Sub CopyNthRows()
Dim wksFrom As Worksheet
Dim wksTo As Worksheet
Dim rngFrom As Range
Dim rngTo As Range

Set wksFrom = ActiveSheet
Set wksTo = Worksheets.Add
Set rngFrom = wksFrom.Range("A3")
Set rngTo = wksTo.Range("A1")

Do While rngFrom.Value <> Empty
rngFrom.EntireRow.Copy rngTo
Set rngTo = rngTo.Offset(1, 0)
Set rngFrom = rngFrom.Offset(intOffsetRows, 0)
Loop

End Sub

intOffsetRows is your Nth variable... This code assumes that Column A is
populated in all instances. If that is wrong then you can change whci row it
checks by changing the:

Set rngFrom = wksFrom.Range("A3")

Or you could make this into a straight for 1 to 30000 loop.

HTH

 

[Stuart]

Sub copyNthRow()
Dim j As Integer
Dim i As Integer
Dim NthRow As Integer

NthRow = 5
j = Cells.SpecialCells(xlLastCell).Row
Range("A1").Select
Do Until ActiveCell.Row > j
Rows(ActiveCell.Row).Copy
Sheets("Sheet2").Range("A1").Offset(i, 0).PasteSpecial (xlValues)
i = i + 1
ActiveCell.Offset(NthRow, 0).Select
Loop
End Sub


If all your stuff is on "Sheet1" then the above sub will copy every 5th row
to "sheet 2"

just change the variable "NthRow" to what ever you need

 

[Guest]

This is good code, but you should probably change i and j to long. Integers
die at around 32,700. Otherwise this should work just fine...

 

[Stuart]

Thanks for that Jim, you are quite right I should have used "Long"

 

[Hal Innes]

Jim and Stuart,

Thank you for the sample code. I'll tinker with the code as I have the first
two rows which are data headers. Our data acquisition system has a blackbox
feature which was running at 100 Hz. I only need 10 Hz sample rate to do the
diagnostics required. Hopefully I won't have to do this data reduction again
as I've slowed the blackbox data rate to 10 Hz.

Hal

 

[bburden752]

This code is just what I been looking for, the only problem is that I cant get it to stop after it copy from sheet1 to sheet2 it just keep looping.

Thanks for your help