Union of a Range is not Working in a VBA function

Question

I tried the accepted answer of Loop through cells and add to a range with a little bit of variation, but my Range Arr was never appended.

When I tried to debug through it, it was only the first range. The Union never worked. Why is this?

Source code:

Public Function VisibleRows(InRange As Range) As Range
    Dim R As Range
    Dim Arr As Range
    Dim RNdx As Integer
    Dim Count As Integer

    For RNdx = 1 To InRange.Rows.Count
        Set R = InRange(RNdx)
        If R.EntireRow.Hidden = False And R.Value2 <> "" Then
            If Arr Is Nothing Then
                Set Arr = R
            Else
                Set Arr = Union(Arr, R)
            End If
        End If
    Next RNdx
    VisibleRows = Arr
End Function

No need to shout. I can understand your frustration but using bold uppercase letters won't make a difference in receiving a response. — Kostas K., Jun 21 '18 at 12:27
If you put `Debug.Print R.Address` immediately before the line `If Arr Is Nothing Then`, you would be able to see what cells your if-statement is capturing. — John Coleman, Jun 21 '18 at 12:47
change `Set R = InRange(RNdx)` to `Set R = InRange.Cells(RNdx)`, change `For RNdx = 1 To InRange.Rows.Count` to `For RNdx = 1 To InRange.Cells.Count` and `VisibleRows = Arr` to `Set VisibleRows = Arr` — tsdn, Jun 21 '18 at 13:01

score 3 · Accepted Answer · answered Jun 21 '18 at 12:34

I can see a couple of problems with the code:

You're looping row by row, but the expression InRange(RNdx) takes the RNdx'th cell within the range - it goes horizontally first and then vertically. You probably want InRange.Cells(RNDx, 1)
Should be Set VisibleRows = Arr

score 2 · Answer 2 · answered Jun 21 '18 at 12:55

Your function is returning a Range Object. Range objects are assigned to a variable with the word Set. You are not using this word. Try this, running TestMe().

Option Explicit

Public Sub TestMe()

    VisibleRows(Range("A1:A10")).Select

End Sub

Public Function VisibleRows(InRange As Range) As Range

    Dim R As Range
    Dim Arr As Range
    Dim RNdx As Integer
    Dim Count As Integer

    For RNdx = 1 To InRange.Rows.Count
        Set R = InRange(RNdx)
        If R.EntireRow.Hidden = False And R.Value2 <> "" Then
            If Arr Is Nothing Then
                Set Arr = R
            Else
                Set Arr = Union(Arr, R)
            End If
        End If
    Next RNdx
    Set VisibleRows = Arr

End Function

This is the sample result from it:

That'll work on a single column, but if the range is `A1:B10` it will only highlight the first five rows if no rows are hidden and all contain a value. As @igorsp7 has - `InRange(RNdx,1)` will check the first column of the range. — Darren Bartrup-Cook, Jun 21 '18 at 14:03

Union of a Range is not Working in a VBA function

2 Answers2