copy range to a variable.

  • Thread starter Thread starter Brian S
  • Start date Start date
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
 
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
 
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
 
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
 
Since you are using xlDown and xlToRight, wouldn't this be equivalent to the
Set statement you posted?

Set myRange = Range("A1").CurrentRegion
 
Gary, even this might be what the OP is looking for ()..

Activesheet.usedrange.select

OR

Dim myRange as Range
Set myRange = ActiveSheet.UsedRange
 
Back
Top