Table of Contents
To check if a cell is blank in VBA, you can use the IsEmpty() function. This function will return a boolean true or false depending on whether the cell is empty or not. You simply need to specify the cell you wish to check as the argument in the IsEmpty() function. For example, IsEmpty(Range(“A1”)) will check if the contents of cell A1 are empty.
You can use the following basic syntax to check if a cell is blank in VBA:
Sub CheckBlank()
Dim i As Integer
For i = 2 To 13
If IsEmpty(Range("A" & i)) Then
Result = "Cell is Empty"
Else
Result = "Cell is Not Empty"
End If
Range("B" & i) = Result
Next i
End Sub
This particular example checks if each cell in the range A2:A13 is blank and then assigns either “Cell is Empty” or “Cell is Not Empty” to each corresponding cell in the range B2:B13.
The following example shows how to use this syntax in practice.
Example: How to Check if Cell is Blank Using VBA
Suppose we have the following list of basketball team names in Excel:
Suppose we would like to check if each cell in the range A2:A13 is blank and then output the results in the corresponding cells in the range B2:B8.
We can create the following macro to do so:
Sub CheckBlank()
Dim i As Integer
For i = 2 To 13
If IsEmpty(Range("A" & i)) Then
Result = "Cell is Empty"
Else
Result = "Cell is Not Empty"
End If
Range("B" & i) = Result
Next i
End Sub
When we run this macro, we receive the following output:
Column B tells us whether or not each of the corresponding cells in column A is empty.
You can also use the following macro to simply return the team name itself in column B if the value is not empty in column A:
Sub CheckBlank()
Dim i As Integer
For i = 2 To 13
If IsEmpty(Range("A" & i)) Then
Result = "Cell is Empty"
Else
Result = Range("A" & i).Value
End If
Range("B" & i) = Result
Next i
End Sub
When we run this macro, we receive the following output:
Note: You can find the complete documentation for the VBA IsEmpty method .