CODEWITHSUNDEEP
vbaexcelrangeunion
Pikachu620
Pikachu620

Reputation: 483

Union of a Range is not Working in a VBA function

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

Upvotes: 2

Views: 2330

Answers (2)

Vityata
Vityata

Reputation: 43575

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:

enter image description here

Upvotes: 2

igorsp7
igorsp7

Reputation: 451

I can see a couple of problems with the code:

  1. 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)

  2. Should be Set VisibleRows = Arr

Upvotes: 3

Related Questions