To check if a cell is empty you can use VBA’s ISEMPTY function. In this function, you need to use the range object to specify the cell that you want to check, and it returns true if that cell is empty, otherwise false. You can use a message box or use a cell to get the result.
Use VBA to Check IF a Cell is Empty
- Start with the function name “IsEmpty”.
- Specify the cell that you want to check.
- Use a message box or a cell to get the result value.
- In the end, run the code.
MsgBox IsEmpty(Range("A1"))
Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook
Check IF Multiple Cells Empty
If you want to check and count the empty cells from a range when you need to loop through each cell in the range.
Sub vba_check_empty_cells()
Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A1:A10")
For Each myCell In myRange
c = c + 1
If IsEmpty(myCell) Then
i = i + 1
End If
Next myCell
MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."
End Sub
The above code loops through each cell in the range A1:A10 and check each cell one by one using the ISEMPTY function if it’s empty or not. And for each empty cell it takes a count, and in the end, shows a message box with the total number of cells and empty cells out of that.
Use the following code if you want to highlight empty cells as well.
Dim i As Long
Dim c As Long
Dim myRange As Range
Dim myCell As Range
Set myRange = Range("A1:A10")
For Each myCell In myRange '
c = c + 1
If IsEmpty(myCell) Then
myCell.Interior.Color = RGB(255, 87, 87)
i = i + 1
End If
Next myCell
MsgBox _
"There are total " & i & " empty cell(s) out of " & c & "."
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Row using VBA in Excel
- How to Insert a Column using VBA in Excel