VBA to check if all cells in a range are empty before proceeding further

I would like to apply a check before executing next lines of code.

I wrote a simple structure like this, but it checks the first cell in range and if that cell is empty it goes to executing next step because of met requirements and the GoTo label.

What I need it to do is to check ALL of the cells, and proceed only if ALL are empty (not to go to next step after checking each cell individually). If one of them is not empty - the sub should exit.

Is it possible to do it using such a code or do I need a different approach in this case?

Sub Check_and_execute
Dim Cell As Range

    For Each Cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        If Cell.Value = "" Then
            GoTo NextStep
        Else
            MsgBox "Not all cells are empty."
            GoTo EndSub
        End If
    Next

NextStep:
'code

EndSub:
End Sub

Rayearth

Posted 2019-12-27T19:42:42.277

Reputation: 123

1Just wondering: Is a cell considered empty only if there is nothing in it? Or, as suggested by your code, would it still be considered empty if it contains a formula that returns a null string ""? – Ron Rosenfeld – 2019-12-27T20:52:09.473

Good point Ron. Application.CountA will count zero length strings (e.g. "") – Jeeped – 2019-12-28T09:22:20.187

@RonRosenfeld Yeah, good point. In my case there are formulas in those cells that return a null string as you described. – Rayearth – 2019-12-29T11:24:15.247

@Rayearth Well, should those be considered empty? or not? if they are returning a null string. – Ron Rosenfeld – 2019-12-29T11:54:20.437

@RonRosenfeld Yes, those cells should be considered as empty. – Rayearth – 2019-12-29T17:07:27.053

@Rayearth See my suggestion with a single line of code to do the test. – Ron Rosenfeld – 2019-12-30T02:34:33.237

Answers

Something like this?

Sub Check_and_execute
Dim Cell As Range
Dim CellsEmpty as boolean
CellsEmpty = True

For Each Cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
    If Cell.Value <> "" Then 
        CellsEmpty = False
        Exit for
    End if
Next

If CellsEmpty = True then
    'code
Else
    MsgBox "Not all cells are empty."
End if

End Sub

cybernetic.nomad

Posted 2019-12-27T19:42:42.277

Reputation: 4 469

Thank you. This solution works. Your method check each cell individually and decides what to do next based on a summarized result. I'm just wondering if a faster method can be used to examine range of cells' value. – Rayearth – 2019-12-29T11:24:41.480

No loop needed:

Sub Check_and_execute()
    If Application.WorksheetFunction.CountA(ThisWorkbook.Sheets("Sheet1").Range("A1:A10")) > 0 Then
        MsgBox "Not all cells are empty."
        Exit Sub
    End If

    'code
End Sub

Scott Craner

Posted 2019-12-27T19:42:42.277

Reputation: 16 128

This also works, but in my case I have formulas resulting with null strings as Ron mentioned in his comment, therefore Application.CountA is returning the MsgBox that not all cells are empty even if they show no value. – Rayearth – 2019-12-29T11:29:57.407

I'm not sure if this is faster, but it is shorter. You can check for a range being blank (including counting null strings as blank) with a single line of code:

rg.Cells.Count = WorksheetFunction.CountBlank(rg)

And you could write this as a function to be called when you need to test a range for being all blanks:

Function allBlank(rg As Range) As Boolean
    allBlank = (rg.Cells.Count = WorksheetFunction.CountBlank(rg))
End Function

And you can use it in your macro as:

Sub Check_and_execute()
    If Not allBlank(ThisWorkbook.Sheets("Sheet1").Range("A1:A10")) Then
        MsgBox "Not all cells are empty"
        Exit Sub
    Else
        'your code
        ' ...
        ' ...
    End If
End Sub

Ron Rosenfeld

Posted 2019-12-27T19:42:42.277

Reputation: 3 333

From the looks of it, this should be more efficient. Thank you Ron for inquisitiveness on precise details and exhausting solutions :) – Rayearth – 2020-01-01T15:56:22.220