Detect if range is empty

Question

I want to check if a range in Excel is empty   How do I write in VBA code   If Range  A38   P38   is empty

User · Answer

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 = True

End 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.

User · Answer

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

User · Answer

This single line works better imho   Application Evaluate  SUMPRODUCT -- E10 E14 lt  gt        0     in this case  it evaluates if range E10 E14 is empty

User · Answer

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

User · Answer

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

User · Answer

Dim M As Range      Set M   Selection  If application CountIf M    lt  gt 0    lt  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  lt  2  Then End If    Clarification to be provided a bit later  right now I m working

User · Answer

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

User · Answer

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

[excel] Detect if range is empty

Examples related to excel

Examples related to vba

Examples related to range