A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Select alternate rows to copy



 
 
Thread Tools Display Modes
  #1  
Old January 26th 05, 05:11 PM
Christina
external usenet poster
 
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
Ads
  #2  
Old January 26th 05, 05:20 PM
Nick Hodge
external usenet poster
 
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 (Data>Filter>Autofilter) 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  
Old January 26th 05, 05:25 PM
Peo Sjoblom
external usenet poster
 
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
data>filter>autofilter, 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  
Old January 27th 05, 01:05 AM
Christina
external usenet poster
 
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 (Data>Filter>Autofilter) 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  
Old January 27th 05, 01:05 AM
Christina
external usenet poster
 
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
> data>filter>autofilter, 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 02:30 PM.


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