Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Christina
 
Posts: n/a
Default 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   Report Post  
Nick Hodge
 
Posts: n/a
Default

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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Christina
 
Posts: n/a
Default

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   Report Post  
Christina
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy qualifying rows to another spreadsheet acpharmd Excel Worksheet Functions 1 December 29th 04 10:45 PM
Unable to select rows in the repeat rows on top option Noppie Excel Discussion (Misc queries) 2 December 28th 04 04:17 PM
Select all data, multiple rows George Wilson Excel Discussion (Misc queries) 6 December 5th 04 09:16 PM
Use of Exact(or other) function for alternate rows? yusee_ygs Excel Worksheet Functions 2 November 3rd 04 09:44 PM
Identifying exact values in alternate rows YG Excel Worksheet Functions 1 November 3rd 04 12:25 AM


All times are GMT +1. The time now is 07:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"