It’s a useful check to know whether there are any empty cells in a dataset or not.
With VBA, you can easily do this using the ISEMPTY function.
In this article, I will show you some simple VBA codes you can use to check if a cell is empty or not.
Check if a Specific Cell is Empty
There are two ways you can check whether a cell is empty or not:
- Using the ISEMPTY function
- Using the equal-to comparison to a blank string
Let’s look at both of these methods
Using ISMPTY Function
Below is the VBA code that checks whether cell A1 is empty or not.
If it is, it shows a message box saying “Cell is Empty”; else, it shows a message box saying “Cell is not Empty”.
Sub CheckIfCellIsEmpty()
Dim targetCell As Range
Dim ws As Worksheet
'Set the worksheet and target cell
Set ws = ThisWorkbook.Sheets("Sheet1")
Set targetCell = ws.Range("A1")
'Using IsEmpty function
If IsEmpty(targetCell.Value) Then
MsgBox "Cell is Empty"
Else
MsgBox "Cell is not Empty"
End If
End SubThe above VBA code uses the IsEmpty function to check whether cell A1 in Sheet1 is empty or not. If it’s empty, it shows a message box as shown below:
Note: In the above example code, I have hardcoded “Sheet1” as the worksheet to check. You can change this or use Activesheet if you want to check in the currently active sheet
Using Comparison Operator
While using the ISEMPTY function is a perfectly fine way to do this, let me also show you another way to check whether a cell is empty or not using VBA.
Below is the VBA code that compares the value in the target cell with an empty string.
If the cell is blank, it shows a message box saying “Cell is Empty”; else, it shows a message box saying “Cell is not Empty”.
Sub CheckIfCellIsEmpty()
Dim targetCell As Range
Dim ws As Worksheet
'Set the worksheet and target cell
Set ws = ThisWorkbook.Sheets("Sheet1")
Set targetCell = ws.Range("A1")
'Comparing with an empty string
If targetCell.Value = "" Then
MsgBox "Cell is Blank"
Else
MsgBox "Cell is not Blank"
End If
End SubCheck If Any Cell is Empty in the Specified Range
If you want to check whether there is any empty cell in the specified range you can use the below VBA code:
Sub CheckIfEmptyInRange()
Dim targetRange As Range
Dim cell As Range
Dim ws As Worksheet
Dim isEmptyFound As Boolean
'Set the worksheet and target range
Set ws = ThisWorkbook.Sheets("Sheet1")
Set targetRange = ws.Range("A1:D10")
'Initialize isEmptyFound as False
isEmptyFound = False
'Loop through each cell in the target range
For Each cell In targetRange
If isEmpty(cell.Value) Then
isEmptyFound = True
Exit For
End If
Next cell
' Display message based on whether an empty cell was found
If isEmptyFound Then
MsgBox "Found Empty Cells in the Range"
Else
MsgBox "No Empty Cells in the Range"
End If
End SubIn the above code, I have hard-coded the sheet name (Sheet1) and the range (A1:D10). You can change these accordingly based on your requirements.
The above code uses a For Each Next loop to go through each cell in the specified range and then check whether it is empty or not using the ISEMPTY function.
As soon as it finds an empty cell, it exits the loop and shows the message box as shown below:
Also read: Using VLOOKUP in VBA
Check If Any Cell is Empty in Selection
If you want to check for blank cells in the already selected range in your worksheet, you can use the VBA code below.
Sub CheckIfEmptyinSelection()
Dim cell As Range
Dim cellempty As Boolean
'Initialize isEmpty to False
cellempty = False
'Loop through each cell in the selection range
For Each cell In Selection
'Use IsEmpty function to check if the cell is empty
If isEmpty(cell.Value) Then
cellempty = True
Exit For
End If
Next cell
'Display a message box based on the isEmpty value
If cellempty Then
MsgBox "Empty Cells Found in Selection"
Else
MsgBox "All cells are filled"
End If
End Sub
The above code uses a For Next loop to go through each cell in the selection and check whether the cell is empty or not using the ISEMPTY function.
Get a Count of Blank Cells in Selection
Along with checking for blank cells in the selection, if you also want to know how many cells are blank, then you can use the below VBA code.
Sub CountBlankCells()
Dim cell As Range
Dim selectionRange As Range
Dim blankCount As Long
' Initialize blankCount to 0
blankCount = 0
' Set the selectionRange to the currently selected cells
Set selectionRange = Selection
' Loop through each cell in the selection range
For Each cell In selectionRange
' Use IsEmpty function to check if the cell is empty
If IsEmpty(cell.Value) Then
blankCount = blankCount + 1
End If
Next cell
' Display a message box with the count of blank cells
MsgBox "Number of blank cells: " & blankCount
End SubThe above VBA code goes through all the cells in the selection and then shows you a message box that shows the total number of blank cells (as shown below):
These are some of the macro codes that can be used to check if a cell is empty using VBA.
Other articles you may also like:
