CODEWITHSUNDEEP
excelvba
happyD
happyD

Reputation: 146

Union function not working on single cell ranges vba

In this code, I am attempting to build a range cell by cell using the union function.

Function getColumnFromRowIndices(ByRef ws As Worksheet, columnHeader As String, rowIndices() As Long, ByRef headerCells_byHeader As Dictionary) As range

    Dim gCFRI As range 'function alias
    Dim cL As String: cL = getColumnLetterOfCell(headerCells_byHeader(columnHeader))

    If Not IsEmpty(rowIndices) Then

        Dim rI As Variant: For Each rI In rowIndices

            If gCFRI Is Nothing Then Set gCFRI = ws.range(cL & CLng(rI)) _
            Else Set gCFRI = Union(gCFRI, ws.range(cL & CLng(rI)))

        Next rI

    End If

    Set getColumnFromRowIndices = gCFRI

End function

The problem I am having is that the union doesn't build up. When I watch ws.range(cL & CLng(rI)) in the watch window, it lists the new cell I want to add, as expected. But the union reverts to arg1 in this case gCFRI everytime it's called.

What must I do to fix this?

Upvotes: 1

Views: 782

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Move:

Dim gCFRI As range

outside the UDF to the top of the module in which the UDF resides. This will allow the range to "remember" its previous value and "build-up" as you require.

Upvotes: 1

Related Questions