Remember Me?

#1
January 26th 05, 06:11 PM
 Christina Posts: n/a
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
January 26th 05, 06:20 PM
 Nick Hodge Posts: n/a

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
January 26th 05, 06:25 PM
 Peo Sjoblom Posts: n/a

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
January 27th 05, 02:05 AM
 Christina Posts: n/a

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
January 27th 05, 02:05 AM
 Christina Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post acpharmd Excel Worksheet Functions 1 December 29th 04 10:45 PM Noppie Excel Discussion (Misc queries) 2 December 28th 04 04:17 PM George Wilson Excel Discussion (Misc queries) 6 December 5th 04 09:16 PM yusee_ygs Excel Worksheet Functions 2 November 3rd 04 09:44 PM YG Excel Worksheet Functions 1 November 3rd 04 12:25 AM

All times are GMT +1. The time now is 08:26 AM.