If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
How do I copy rows with hidden rows between without unhiding data. | Biggie J | General Discussion | 1 | October 11th, 2004 08:57 PM |
Help with an Inner Join? | Grahammer | Running & Setting Up Queries | 7 | September 20th, 2004 09:56 PM |
Select a row, copy and repeat on the next row | Jason L | General Discussion | 2 | August 18th, 2004 06:33 PM |
Copy and Paste hidden rows | DuncVern | Worksheet Functions | 1 | April 1st, 2004 05:16 PM |