Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Worksheet wont copy objects. | Excel Worksheet Functions | |||
Unable to Insert Worksheet/Move and copy worksheet Excel 2003 | Excel Worksheet Functions | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming |