VBA: How to Use “If Not Blank”


You can use Not IsEmpty in VBA to check if a cell is not blank.

Here’s an example of how you might use this syntax in a macro:

Sub IfNotBlank()
    Dim i As Integer

    For i = 2 To 13
        If Not IsEmpty(Range("A" & i)) Then
        Result = "Cell is Not Empty"
        Else
        Result = "Cell is Empty"
        End If
    Range("B" & i) = Result
    Next i
End Sub

This particular example checks if each cell in the range A2:A13 is not blank and then outputs either “Cell is Not Empty” or “Cell is Empty” to each corresponding cell in the range B2:B13.

The following example shows how to use this syntax in practice.

Example: How to Use “If Not Blank” in 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 not 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 IfNotBlank()
    Dim i As Integer

    For i = 2 To 13
        If Not IsEmpty(Range("A" & i)) Then
        Result = "Cell is Not Empty"
        Else
        Result = "Cell is 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 IfNotBlank()
    Dim i As Integer

    For i = 2 To 13
        If Not IsEmpty(Range("A" & i)) Then
        Result = Range("A" & i).Value
        Else
        Result = "Empty"
        End If
    Range("B" & i) = Result
    Next i
End Sub

When we run this macro, we receive the following output:

Column B now returns the name of the team in column A if the cell is not blank.

Note: You can find the complete documentation for the VBA IsEmpty method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: A Formula for “If” Cell Contains”

Leave a Reply

Your email address will not be published. Required fields are marked *