VBA Range Union Problem

[randomwit]

Here's my function that isn't working:

For some reason, I never get past the Union line. I thought it was because my range was empty, but my workaround using a binary flag didn't work either.

I am trying to sort through RangeOfCells, and return an address that represents all cells but the ones that are colored the integer ColorIndex.

The color detection works, and my inputs seem fine, but I can't add the non colored cells to the "empty" array.

HELP!

Function ExcludeColoredCellRange(RangeOfCells As Range, ColorIndex As Long)
'returns a subrange of cells within RangeOfCells that have the background color ColorIndex
'RangeOfCells must be one column for routine to work correctly

    Dim N As Long
    Dim MyRange As Range
'    Dim flag As Long 'workaround to empty array problem
    
'    flag = 0
    
    MsgBox ("Range Input: " & RangeOfCells.Address(ReferenceStyle:=xlA1)) 'debug
    MsgBox ("Length: " & RangeOfCells.Count()) 'debug
    
        For N = 1 To RangeOfCells.Rows.Count
            MsgBox ("N = " & N) 'debug
            If Not (RangeOfCells.Cells(N, 1).Interior.ColorIndex = ColorIndex) Then
                MsgBox ("IT'S NOT THAT COLOR! ADD IT!")
'                If flag = 0 Then
'                    Set MyRange = Range(RangeOfCells.Cells(N, 1))
'                    flag = 1
'                    MsgBox ("MyRange EXISTS NOW!")
'                Else
                    Set MyRange = Union(MyRange, Range(RangeOfCells.Cells(N, 1)))
                    MsgBox ("STILL ALIVE AFTER UNION?") 'debug
'                End If
            End If
            MsgBox ("MADE IT OUT!") 'debug
        Next N
    
    MsgBox ("Range Output: " & SubRange.Address(ReferenceStyle:=xlA1)) 'debug
    
    ExcludeColoredCellRange = SubRange.Address(ReferenceStyle:=xlA1)
    
End Function

 

[shg]

The output range is Nothing before it's initialized, and Union won't accept Nothing as an argument.

Sub test()
    MsgBox ExcludeColoredCellRange(Range("A1:A10"), 4)
End Sub
 
Function ExcludeColoredCellRange(rInp As Range, iCI As Long) As String
    Dim cell As Range
    Dim rOut As Range
    
    For Each cell In rInp.Cells
        If cell.Interior.ColorIndex <> iCI Then
            If rOut Is Nothing Then Set rOut = cell
            Set rOut = Union(rOut, cell)
        End If
    Next cell
 
    ExcludeColoredCellRange = rOut.Address
End Function

 

[randomwit]

You rock. I thought that initializing was my problem (hence my much less eloquent attempt to initialize the output range using the flag variable), but I wasn't sure how to do it. Now I know the "Is Nothing" syntax. Thanks!

 

[shg]

You're welcome, glad it worked for you.

 

[Rick Rothstein]

@randomwit,

I noticed this comment in your original message...

"RangeOfCells must be one column for routine to work correctly"

Just to point out... the code that 'shg' posted does not suffer from this restriction... you can pass in any range, even an non-contiguous one like Range("A2:C3,B9:D12"), and it will work correctly.