MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying a Range using Variables


Posted by Alane on December 13, 2001 4:55 PM


I'm trying to write a macro to copy a Range based on variables. For example, if the rows are always 1 to 10 I would normally use:

Rows("1:10").Select

I would like to set variables to two cells on a data sheet. The data cells are B1 and B2. Each containing a number. I tried the following:

Dim RowStart As String
Dim RowEnd As String

RowStart = Range("B1")
RowEnd = Range("B2")
Rows(RowStart:RowEnd).Select

I'm obviously missing something...

Thanks for your help!


Posted by Juan Pablo G. on December 13, 2001 4:57 PM

You're not missing much...

Rows(RowStart & ":" & RowEnd).Copy Destination:=Range("A1")

Note, you don't have to select previously to copy, you can do that at once.

Juan Pablo G.

Posted by Russell Hauf on December 13, 2001 5:37 PM

Also....

You can also get rid of the string by using the Range object:

Range(Rows(RowStart),Rows(RowEnd)).Copy Destination:=Range("A1")

-rh You're not missing much... Rows(RowStart & ":" & RowEnd).Copy Destination:=Range("A1") Note, you don't have to select previously to copy, you can do that at once. Juan Pablo G. :