Is there a way to get the column indexes from VBA RefEdit for selected (multiple) ranges?-CodePudding

In an UserForm I would like to select one or multiple ranges from a bigger data set via RefEdit control. The selected range(es) are the columns I want to perform the analysis with.

If I select for example multiple ranges I get following RefEdit value:

data!$A$1:$A$2;data!$C$1:$F$2

What I need would be instead an array with the values:

1,3,4,5,6

Is there a way to get the column number from the letters of this address (even the ones hidden by the statement "C:F" --> C,D,E,F ?

CodePudding user response：

This might get unwieldy if you have large areas, but the example code below will extract unique column numbers from a range. The range in the example has multiple areas joined with Union and captures only the column numbers.

Notice that I included ranges in the Union that overlap in terms of columns, to guarantee the code works for that condition.

Option Explicit

Sub test()
    With Sheet1
        Dim multi As Range
        Set multi = Union(.Range("A1:C3"), _
                          .Range("F7:H18"), _
                          .Range("C11:D14"))
        '--- Select is NOT necessary!
        '    only here to visualize the area
        multi.Select
    End With
    
    Debug.Print "Multi areas: " & multi.Address
    
    Dim columnsUsed As Collection
    Set columnsUsed = New Collection
    
    '--- adding a duplicate column number will cause an error
    '    the resulting Collection
    On Error Resume Next
    Dim item As Range
    For Each item In multi
        columnsUsed.Add item.Column, CStr(item.Column)
    Next item
    On Error GoTo 0
    
    '--- column numbers are not sorted in the collection
    '    but you can sort if required
    Dim columnNumber As Variant
    For Each columnNumber In columnsUsed
        Debug.Print columnNumber
    Next columnNumber
End Sub

Resulting output:

Multi areas: $A$1:$C$3,$F$7:$H$18,$C$11:$D$14
 1 
 2 
 3 
 6 
 7 
 8 
 4