Excel Visual Basic - detect if range is empty

Question

I don't know if it's possible, but I want to check if a range in Excel is empty. So how do I write if:

Range("A38":"P38")

Is empty in VBA code?

Thanks in advance.

Answer 1

Found a solution from the comments I got.

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

Answer 2

Dim M As Range

    Set M = Selection

If application.CountIf(M, "<>0") < 2 Then
    MsgBox "Nothing selected, please select first BOM or Next BOM"
Else

'Your code here

End If

From experience I just learned you could do:

If Selection.Rows.Count < 2 
Then End If`

Clarification to be provided a bit later (right now I'm working)

Answer 3

Another possible solution. Count empty cells and subtract that value from the total number of cells

Sub Emptys()

Dim r As range
Dim totalCells As Integer

'My range To check'
Set r = ActiveSheet.range("A1:B5")

'Check for filled cells'
totalCells = r.Count- WorksheetFunction.CountBlank(r)


If totalCells = 0 Then
    MsgBox "Range is empty"
Else
    MsgBox "Range is not empty"
End If

End Sub

Answer 4

If you find yourself in a situation where it's absolutely necessary that you loop through each cell in a range instead of using CountA, then it's much faster to first pull that range of data into an array and loop on that array's values instead of the cells' values themselves. You can send this function a range to get back a true/false result.

Function RangeIsEmpty(ByVal rng As Range) As Boolean

    Dim area As Range
    For Each area In rng.areas

        Dim arr As Variant
        arr = area.value

        For i = LBound(arr, 2) To UBound(arr, 2)        'columns
            For j = LBound(arr, 1) To UBound(arr, 1)    'rows

                'if cell is not empty then
                If Len(Trim(arr(j, i))) > 0 Then
                    RangeIsEmpty = False
                    Exit Function
                End If

            Next j
        Next i

    Next area

    RangeIsEmpty = True

End Function

---

Example of how to use it:

Sub Test()
    Debug.Print RangeIsEmpty(Range("A38:P38"))
End Sub

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

Answer 5

Dim cel As Range, hasNoData As Boolean

    hasNoData = True
    For Each cel In Selection
        hasNoData = hasNoData And IsEmpty(cel)
    Next

This will return True if no cells in Selection contains any data. For a specific range, just substitute RANGE(...) for Selection.

Answer 6

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.