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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 05:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com