Copying a variable range
 

Hi!
I am trying to copy a range that always starts in row 3 but can vary in
how many rows down it goes (up to 40). I am currently using a series of
if thens and a count cell reference. I'm sure there is a much more
efficient method but I'm still a novice at this. Any help would be
appriciated.


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=476763

Copying a variable range
 

On Mon, 17 Oct 2005 07:02:50 -0500, Brian Matlack
wrote:


Hi!
I am trying to copy a range that always starts in row 3 but can vary in
how many rows down it goes (up to 40). I am currently using a series of
if thens and a count cell reference. I'm sure there is a much more
efficient method but I'm still a novice at this. Any help would be
appriciated.


Have you tried the CurrentRegion property of the Range object?

e.g. Range("A3").CurrentRegion

Rgd
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Copying a variable range
 

Dim rng as Range
set rng = Range(cells(3,1),cells(3,1).End(xldown))
rng.Entirerow.copy Destination:=worksheets("Sheet2").Range("A1")

--
Regards,
Tom Ogilvy

"Brian Matlack"
wrote in message
news:Brian.Matlack.1x1pac_1129550707.5155@excelfor um-nospam.com...

Hi!
I am trying to copy a range that always starts in row 3 but can vary in
how many rows down it goes (up to 40). I am currently using a series of
if thens and a count cell reference. I'm sure there is a much more
efficient method but I'm still a novice at this. Any help would be
appriciated.


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile:
http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=476763

Copying a variable range
 

Just a caution,

Current region could easily expand up to rows 2 and or 1 if there is data
there.

To test, select A3 and do Ctrl+Shift+8

and see what is selected.

--
Regards,
Tom Ogilvy


"Richard Buttrey" wrote in
message ...
On Mon, 17 Oct 2005 07:02:50 -0500, Brian Matlack
wrote:


Hi!
I am trying to copy a range that always starts in row 3 but can vary in
how many rows down it goes (up to 40). I am currently using a series of
if thens and a count cell reference. I'm sure there is a much more
efficient method but I'm still a novice at this. Any help would be
appriciated.


Have you tried the CurrentRegion property of the Range object?

e.g. Range("A3").CurrentRegion

Rgd
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Copying a variable range
 

Ihave a thought,
before you record your macro have your cell anywhere but at the
beggining.
now click on record,(you must be in absolute mode) go to the top of the
row you want to copy ,(now,change the macro to relative mode) press
end-down to take you to the bottom of the row stop recording
test out your macro to see if it works

Copying a variable range
 

On Mon, 17 Oct 2005 08:41:05 -0400, "Tom Ogilvy"
wrote:

Just a caution,

Current region could easily expand up to rows 2 and or 1 if there is data
there.

To test, select A3 and do Ctrl+Shift+8

and see what is selected.

Good point Tom. Caught me out a few times that one :-(

Cheers.
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Copying a variable range
 

Thanks for all the help folks!! I think this help forum is great. I hope
someday I'm good enough to return the favors. Maybe this internet thing
is going to catch-on after all heh! Thanks again


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=476763