VBA to check if all cells in a range are empty before proceeding further
Solution 1
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
Solution 2
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
Solution 3
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
Related videos on Youtube
![Rayearth](https://i.stack.imgur.com/aMMfZ.jpg?s=256&g=1)
Rayearth
Updated on September 18, 2022Comments
-
Rayearth almost 2 years
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
-
Ron Rosenfeld over 4 yearsJust 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
""
? -
Admin over 4 yearsGood point Ron. Application.CountA will count zero length strings (e.g.
""
) -
Rayearth over 4 years@RonRosenfeld Yeah, good point. In my case there are formulas in those cells that return a null string as you described.
-
Ron Rosenfeld over 4 years@Rayearth Well, should those be considered empty? or not? if they are returning a null string.
-
Rayearth over 4 years@RonRosenfeld Yes, those cells should be considered as empty.
-
Ron Rosenfeld over 4 years@Rayearth See my suggestion with a single line of code to do the test.
-
-
Rayearth over 4 yearsThank 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 over 4 yearsThis 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 over 4 yearsFrom the looks of it, this should be more efficient. Thank you Ron for inquisitiveness on precise details and exhausting solutions :)