Array of Range Objects

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

Is it possible to create an array of range objects? I have used the Set stmt
for range objects (Example #1). Is it possible to set up many rang objects
in an array and use the Set stmt on this array(Example #2)? If so how would
you dimension the array?

Example #1
Set rng = ........

Example#2
For X = 0 to 10
Set RngArray(0) = .....
Next
 
You could set up your array to hold variants which will allow for ranges but
a collection is generally a little easier when you want to store a group of
objects. Something like this...

Sub test()
Dim colRanges As Collection
Dim rng

Set colRanges = New Collection
With colRanges
.Add Range("A1:A10")
.Add Range("B1:B10")
.Add Range("C1:C10")
End With

For Each rng In colRanges
MsgBox rng.Address
Next rng
End Sub
 
Yes, it is possible; but, as Jim said, a collection is easier to use. Here
are two examples showing you the answer to your question...

Sub DynamicRangeArray()
Dim Rng() As Range
ReDim Rng(1 To 3)
Set Rng(1) = Cells(1, 2)
Set Rng(2) = Range("D4:F20")
Set Rng(3) = Columns("G:G")
MsgBox Rng(1).Address & vbLf & Rng(2).Address & vbLf & Rng(3).Address
End Sub

Sub FixedRangeArray()
Dim Rng(1 To 3)
Set Rng(1) = Cells(1, 2)
Set Rng(2) = Range("D4:F20")
Set Rng(3) = Columns("G:G")
MsgBox Rng(1).Address & vbLf & Rng(2).Address & vbLf & Rng(3).Address
End Sub

Rick
 
Back
Top