Union SpecialCells for formulas and constants

[YasserKhalil]

Hello everyone
I am trying to deal with formulas and constants at the same time in a specific column. I have used Union to include both formulas and constants like that

Code: Select all

        Dim rng As Range
        On Error Resume Next
        With ws.Columns("P")
    Set rng = Union(.SpecialCells(xlCellTypeConstants), .SpecialCells(xlCellTypeFormulas))
End With
On Error GoTo 0

The code is working well when the column has both constants and formulas and rng returns a real range. But when the column has only one of the both (formulas or constants), the rng returns Nothing.
Any idea how to handle such a case?

[HansV]

Code: Select all

    Dim rngC As Range
    Dim rngF As Range
    Dim rng As Range
    On Error Resume Next
    With ws.Range("P:P")
        Set rngC = .SpecialCells(xlCellTypeConstants)
        Set rngF = .SpecialCells(xlCellTypeFormulas)
    End With
    On Error GoTo 0
    If rngC Is Nothing Then
        If rngF Is Nothing Then
            ' Column P is empty, so rng is Nothing
        Else
            ' Only formulas
            Set rng = rngF
        End If
    ElseIf rngF Is Nothing Then
        ' Only constants
        Set rng = rngC
    Else
        ' Both constants and formulas
        Set rng = Union(rngC, rngF)
    End If

[YasserKhalil]

Thanks a lot. It is long somewhat, not as I expected. Is it possible to change the code to a UDF or sub procedure, as I will use such an idea more than three times.

[HansV]

Function:

Code: Select all

Function GetFilledRange(MyRange As Range) As Range
    Dim rngC As Range
    Dim rngF As Range
    Dim rng As Range
    On Error Resume Next
    With MyRange
        Set rngC = .SpecialCells(xlCellTypeConstants)
        Set rngF = .SpecialCells(xlCellTypeFormulas)
    End With
    On Error GoTo 0
    If rngC Is Nothing Then
        If rngF Is Nothing Then
            ' Column P is empty, so result is Nothing
        Else
            ' Only formulas
            Set GetFilledRange = rngF
        End If
    ElseIf rngF Is Nothing Then
        ' Only constants
        Set GetFilledRange = rngC
    Else
        ' Both contants and formulas
        Set GetFilledRange = Union(rngC, rngF)
    End If
End Function

Usage:

Code: Select all

    Dim rng As Range
    Set rng = GetFilledRange(Worksheets("Sheet1").Range("P:P"))
    If rng Is Nothing Then
        ...
    Else
        ...
    End If

[YasserKhalil]

Thank you very much for your great support.