VBA: Array of Range References

Question

In VBA, I am trying to create an array of Range References. Here's my current attempt:

Dim columnName()  As String
Dim colIndex()    As Long
Dim colRange()    As Range

colCount = 10
ReDim columnName(colCount)
ReDim colIndex(colCount)
ReDim colRange(1 To colCount)

columnName(ID) = "ID"
'etc

For i = 1 To UBound(columnName)
    colIndex(i) = WS.Range("A1", "ZZ1").Find(columnName(i), LookIn:=xlValues, MatchCase:=False).column
    colRange(i) = WS.Range(Cells(2, colIndex(i)), Cells(LastRowIndex, colIndex(i)))
    If 1 = 1 Then 'debugging
        Application.ScreenUpdating = True
        Debug.Print colRange(i).Value
        Debug.Print colRange(i).Address
        colRange(i).Select
        Application.ScreenUpdating = False
    End If

When I try to store multiple references in an array, I get something like this:

i = 1
colIndex(i) = 8
Cells(2, colIndex(i)) = 123
Cells(LastRowIndex, colIndex(i)) =789
colRange(i) = Nothing

I have tried making colRange a variant, but nothing seems to work. No solutions I found via google or StackOverflow seemed to address this.

Change Dim colRange() As Range to Dim colRange() As String — Siddharth Rout
– Siddharth Rout, Commented Aug 18, 2014 at 21:07
When I did that, it just gives Compile error: Invalid qualifier for the Debug.Print statements and the colRange(i).Select statement. — Phillip
– Phillip, Commented Aug 18, 2014 at 21:10
I added few other things so you may have to refresh the page — Siddharth Rout
– Siddharth Rout, Commented Aug 18, 2014 at 21:15

Community · Accepted Answer · 2017-05-23 12:29:26Z

1

Further to my comment above, here is an example

Sub Sample()
    Dim columnName() As String
    Dim rng As Range

    colCount = 10
    ReDim columnName(colCount)

    ID = 1

    columnName(ID) = "A"

    'MsgBox Cells(1, columnName(1)).Address
    Cells(1, columnName(1)).Value = "Blah Blah"

    Set rng = Cells(1, columnName(1))

    With rng
        MsgBox .Address
        .Value = "Something"
        '~~> Do whatever you want to do with that range here
    End With
End Sub

Avoid the use of .Select. Directly perform the action on the range like I have done above. INTERESTING READ

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Aug 18, 2014 at 21:10

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Phillip Over a year ago

I feel stupid. I played with your code, change rng to rng(), change Set rng... to Set rng(1) = Range(...), and kept comparing it to mine. Eventually saw the tiny difference: I was using colRange(i) = Range(...) when I needed to be using SET colRange(i) = Range(...). Thank you for taking the time to write up this example, it was certainly helpful. Thanks for the link as well!

Collectives™ on Stack Overflow

VBA: Array of Range References

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related