Detect if range is empty

If you find yourself in a situation where you can’t use CountA then it's much faster to first store your range as an array and loop on the array's data than it is to individually loop on range/cell data.

Function IsRangeEmpty(ByVal rng As Range) As Boolean
    
    ''Returns true if a value is found in parameter range.
    ''Converts parameter range to an array to check it quickly.
    
    'if rng has cells in it then
    If Not rng Is Nothing Then
    
        Dim area As Range
        For Each area In rng.Areas 'checks for range with multiple cell groups e.g., rng=Range("A1:B5,C6:D9")
            
            'if rng has more than one cell then
            If area.Cells.Count > 1 Then
            
                'save range as array
                Dim arr As Variant
                arr = area.value
                
                'loop through array
                Dim arrCell As Variant
                For Each arrCell In arr
                
                    'if cell is not empty then
                    If Len(Trim(arrCell)) > 0 Then
                        IsRangeEmpty = False
                        Exit Function
                    End If
    
                Next arrCell
                
            Else 'unnecessary to loop on a single cell
                
                'if cell is not empty then
                If Len(Trim(area.Value2)) > 0 Then
                    IsRangeEmpty = False
                    Exit Function
                End If
                
            End If
    
        Next area
    End If
    
    IsRangeEmpty = True

End Function

---

Example of how to use it:

Sub debug_IsRangeEmpty()
    Debug.Print IsRangeEmpty(Range("A38:P38"))
End Sub

If Range("A38:P38") is empty, it would print True in the Immediate Window; otherwise it'd print False.

Found a solution from the comments I got.

Sub TestIsEmpty()
    If WorksheetFunction.CountA(Range("A38:P38")) = 0 Then
        MsgBox "Empty"
    Else
        MsgBox "Not Empty"
    End If
End Sub