VBA - Define range variables through looping

Question

I'm wondering if it's possible to define range variables through a for..next loop?

Something like that

Public Sub DefineRanges()
 Dim i As Long
 Dim rngLine1, rngLine2 As Range

 For i = 1 To 2
    Set Replace("rngLinex", "x", i) = ThisWorkbook.Sheets("Sheet1").Range("A" & i)
 Next i
End Sub

or like that

Public Sub DefineRanges()
 Dim i As Long
 Dim rngLine1, rngLine2 As Range

 For i = 1 To 2
    Set rngLine & i = ThisWorkbook.Sheets("Sheet1").Range("A" & i)
 Next i
End Sub

Thank you in advance.


Show source
| excel-vba   2017-01-05 19:01 1 Answers

Answers to VBA - Define range variables through looping ( 1 )

  1. 2017-01-05 19:01

    As @JohnColeman stated use an array of ranges:

    Sub defineranges()
    Dim i As Long
    Dim RngArr(1 To 2) As Range
    
    For i = 1 To 2
        Set RngArr(i) = ThisWorkbook.Worksheets("Sheet1").Range("A" & i)
    Next i
    
    For i = 1 To 2
        Debug.Print RngArr(i).Value
    Next i
    End Sub
    

Leave a reply to - VBA - Define range variables through looping

◀ Go back