Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Select alternate rows to copy
I would like to select alternate rows within a range on a worksheet to copy
to another sheet. Any ideas on how to do this? -- Christina |
#2
|
|||
|
|||
Christina
Use a spare column (I used B but any will do), enter this formula and copy down to the full extent of your data. You will get alternating TRUE, FALSE =MOD(ROW(A2),2)=0 Now invoke an autofilter (DataFilterAutofilter) and filter on which one you want. Copy that data and pate to another sheet. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Christina" wrote in message ... I would like to select alternate rows within a range on a worksheet to copy to another sheet. Any ideas on how to do this? -- Christina |
#3
|
|||
|
|||
One fairly easy way, assume the date is in A2:A200 and you want to copy A2,
A4, A6 and so on, insert a new column adjacent to the one you want to copy (if it is empty already no need to insert a new column), in this case in B2 you would put =MOD(ROW(1:1),2)=1 now copy down using the fillhandle to B200, select both column and apply datafilterautofilter, form the dropdown in B select TRUE, select the visible range and press F5, select special and visible cells only, press ctrl + c to copy and then paste into the other sheet, finally remove the help range from both ranges Or from the other sheet use a formula like =OFFSET(Sheet1!$A$2,ROW(1:1)*2-2,) copy down Regards, Peo Sjoblom "Christina" wrote: I would like to select alternate rows within a range on a worksheet to copy to another sheet. Any ideas on how to do this? -- Christina |
#4
|
|||
|
|||
Thanks Nick. That is exactly what I needed.
"Nick Hodge" wrote: Christina Use a spare column (I used B but any will do), enter this formula and copy down to the full extent of your data. You will get alternating TRUE, FALSE =MOD(ROW(A2),2)=0 Now invoke an autofilter (DataFilterAutofilter) and filter on which one you want. Copy that data and pate to another sheet. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS "Christina" wrote in message ... I would like to select alternate rows within a range on a worksheet to copy to another sheet. Any ideas on how to do this? -- Christina |
#5
|
|||
|
|||
Thanks Peo. That is exactly what I needed.
"Peo Sjoblom" wrote: One fairly easy way, assume the date is in A2:A200 and you want to copy A2, A4, A6 and so on, insert a new column adjacent to the one you want to copy (if it is empty already no need to insert a new column), in this case in B2 you would put =MOD(ROW(1:1),2)=1 now copy down using the fillhandle to B200, select both column and apply datafilterautofilter, form the dropdown in B select TRUE, select the visible range and press F5, select special and visible cells only, press ctrl + c to copy and then paste into the other sheet, finally remove the help range from both ranges Or from the other sheet use a formula like =OFFSET(Sheet1!$A$2,ROW(1:1)*2-2,) copy down Regards, Peo Sjoblom "Christina" wrote: I would like to select alternate rows within a range on a worksheet to copy to another sheet. Any ideas on how to do this? -- Christina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy qualifying rows to another spreadsheet | Excel Worksheet Functions | |||
Unable to select rows in the repeat rows on top option | Excel Discussion (Misc queries) | |||
Select all data, multiple rows | Excel Discussion (Misc queries) | |||
Use of Exact(or other) function for alternate rows? | Excel Worksheet Functions | |||
Identifying exact values in alternate rows | Excel Worksheet Functions |