CODEWITHSUNDEEP
excelvbarange
Kano
Kano

Reputation: 1493

Detect if range is empty

I want to check if a range in Excel is empty.

How do I write in VBA code:

If Range("A38":"P38") is empty

Upvotes: 48

Views: 275541

Answers (8)

Marcucciboy2
Marcucciboy2

Reputation: 3263

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 range has cells in it then
    If Not rng Is Nothing Then
    
        Dim area As Range
        For Each area In rng.Areas 'checks through all sub-ranges within the original range e.g., rng=Range("A1:B5,C6:D9")
            
            'if sub-range 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.

Upvotes: 9

intrixius
intrixius

Reputation: 1136

This single line works better imho:

Application.Evaluate("SUMPRODUCT(--(E10:E14<>""""))=0")

in this case, it evaluates if range E10:E14 is empty.

Upvotes: 0

Kano
Kano

Reputation: 1493

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

Upvotes: 83

linker
linker

Reputation: 891

This just a slight addition to @TomM's answer/ A simple function to check if your Selection's cells are empty

Public Function CheckIfSelectionIsEmpty() As Boolean
   Dim emptySelection As Boolean:emptySelection=True
   Dim cell As Range
   For Each cell In Selection
       emptySelection = emptySelection And isEmpty(cell)
       If emptySelection = False Then
          Exit For
       End If
   Next
   CheckIfSelectionIsEmpty = emptySelection
End Function

Upvotes: 0

TomM
TomM

Reputation: 31

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.

Upvotes: 3

DJK
DJK

Reputation: 9274

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

Upvotes: 2

DeerSpotter
DeerSpotter

Reputation: 433

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)

Upvotes: 4

Sharunas Bielskis
Sharunas Bielskis

Reputation: 1203

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.

Upvotes: 5

Related Questions