Copy every nth row into a new worksheet
 

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

Copy every nth row into a new worksheet
 

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

"Hal" wrote:

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

Copy every nth row into a new worksheet
 

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





"Hal" wrote in message
...
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

Copy every nth row into a new worksheet
 

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

"Hal" wrote:

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

Copy every nth row into a new worksheet
 

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

"Jim Thomlinson" wrote in message
...
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...

"Hal" wrote:

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