copy range to a variable.

B

Brian S

Is it possible to copy a range of cells and set them equal to a variable?
Something like this:
Cells(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
x = selection


Thanks
 
G

Gary Keramidas

this should be all you need

rng = Range("A1", range("A1").End(xlToRight))

open the immediate window in the vb editor (control G) and paste the above line and
press enter

then enter this line and press enter and it should display the range
?rng.address
 
J

Jacob Skaria

You can set them to a Range variable as below

Dim myRange As Range
Set myRange = Range("A1").Resize(Range("A1").End(xlDown).Row, _
Range("A1").End(xlToRight).Column)

myRange.Select
 
G

Gary Keramidas

here is another way, jacob's is shorter

Dim rng2 As Range

Set rng2 = Range(Range("A1", Range("A1").End(xlDown)),
Range(Range("A1").End(xlToRight), _
Range("A1").End(xlToRight).End(xlDown)))
rng2.Select
 
R

Rick Rothstein

Since you are using xlDown and xlToRight, wouldn't this be equivalent to the
Set statement you posted?

Set myRange = Range("A1").CurrentRegion
 
J

Jacob Skaria

Gary, even this might be what the OP is looking for ()..

Activesheet.usedrange.select

OR

Dim myRange as Range
Set myRange = ActiveSheet.UsedRange
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top