Detect if range is empty

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 IfEnd Sub

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 data than it is to loop on range/cell data.

Function IsRangeEmpty(ByVal rng As Range) As Boolean    'Converts a range to an array and returns true if a value is found in said array        Dim area As Range    For Each area In rng.Areas                If area.Cells.Count > 1 Then                    'save range as array            Dim arr As Variant            arr = area.value                        'loop through array            Dim cel As Variant            For Each cel In arr                            'if cell is not empty then                If Len(Trim(cel)) > 0 Then                    IsRangeEmpty = False                    Exit Function                End If            Next cel                    Else    'cannot loop on array with one value                        'if cell is not empty then            If Len(Trim(area.Value2)) > 0 Then                IsRangeEmpty = False                Exit Function            End If                    End If    Next area    IsRangeEmpty = TrueEnd Function

Example of how to use it:

Sub Test()    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.

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants. (https://msdn.microsoft.com/en-us/library/office/gg264227.aspx) . So you must check every cell in range separately:

    Dim thisColumn as Byte, thisRow as Byte    For thisColumn = 1 To 5        For ThisRow = 1 To 6             If IsEmpty(Cells(thisRow, thisColumn)) = False Then                 GoTo RangeIsNotEmpty             End If        Next thisRow    Next thisColumn    ...........    RangeIsNotEmpty: 

Of course here are more code than in solution with CountA function which count not empty cells, but GoTo can interupt loops if at least one not empty cell is found and do your code faster especially if range is large and you need to detect this case. Also this code for me is easier to understand what it is doing, than with Excel CountA function which is not VBA function.