Checking for blank cells in Excel with VBA

This tutorial checks the content of cells to see if they are blank or not. This tutorial includes an introduction to the for each loop to loop through cells in a range as well as code to deal with an error gracefully.

1. Check a specific cell

The below VBA code will check to see if cell A1 is empty or not (paste it into a Module and run it from the Macro button on the Developer toolbar): ^[1]

Option Explicit

Sub myBlankChecker()
'
' this Macro checks to see if cell A1 is blank
'

'
    If IsEmpty("A1") = True Then
            MsgBox ("Cell A1 is not empty")
        Else
            MsgBox ("Cell A1 is empty")
    End If
    
End Sub

2. Check the selected cell

The above code is not particularly useful; let’s update it so that it checks the currently selected cell instead of only cell A1. Even when dealing with a single cell, we are going to use the Range object in VBA. ^[2] Then, we will check each cell in the range to see if is empty or not.

Option Explicit

Sub myBlankChecker()
'
' checks for blank cells in a range
'

'

Dim Cell as Object
Dim rng As Range
Set rng = Selection

For Each Cell In rng

    If IsEmpty(Cell.Value) = True Then
        MsgBox ("Cell is empty")
    Else
        MsgBox ("Cell is not empty")
    End If
Next Cell

End Sub

3. Counting blank cells

Instead of an irritating pop-up for every blank cell found, we will declare a variable and use it to record the number of blank cells and then report in a pop-up at the end of the routine.

Option Explicit

Sub myIntBlankCounter()
'
' counts the number of blank cells in a range
'

'

Dim Cell as Object
Dim rng As Range
Set rng = Selection
Dim countOfBlank As Integer

For Each Cell In rng
    If IsEmpty(Cell.Value) = True Then
        countOfBlank = countOfBlank + 1
    End If
Next Cell

MsgBox ("Number of blank cells: " & countOfBlank)

End Sub

4. Test

Test your code!

What happens if you select an entire column and run your Macro?

1. Select column A
2. Activate the Developer tab
3. Left-click the Macro button
4. Select the myIntBlankCounter Macro
5. Left-click the Run button

What happens if you select the entire sheet and run your Macro?

5. Know your limits

We are going to get into trouble if someone selects a couple of columns — let alone an entire worksheet and runs our Macro!

A single Excel worksheet has 1,048,576 rows and 16,384 columns, for a total of 17,179,869,184 cells. The number of worksheets in a workbook is limited by the available memory. ^[3]

The Long data type in VBA can store values ranging from -2,147,483,648 to 2,147,483,648. This is larger than the Integer data type, which can only store values up to 32,767. ^[4]

Option Explicit

Sub myLongBlankCounter()
'
' count the blank cells in a range but exit before there is a problem with the size of the count!
'

'

Dim Cell As Object
Dim rng As Range
Set rng = Selection
Dim countOfBlank As Long

For Each Cell In rng
    If countOfBlank > 1100000 Then
        MsgBox ("Number cells exceeds 1100000")
        Exit Sub
    ElseIf IsEmpty(Cell.Value) = True Then
        countOfBlank = countOfBlank + 1
    End If
Next Cell

MsgBox ("Number of blank cells: " & countOfBlank)

End Sub

6. Errr… there is a far easier method

Remember Excel’s COUNTBLANK() function? ^[5]

Sub myLongBlankCounter()
'
'
' count the blank cells in a range but exit before there is a problem with the size of the count!
'

'

Dim rng As Range
Set rng = Selection
Dim myBlankTotal As Long

myBlankTotal = Application.WorksheetFunction.CountBlank(rng)

MsgBox ("Count of blank cells is " & myBlankTotal)

End Sub

7. Errr(or)… there is a better way

Of course, we will still have an overflow error ^[6] if the value in myBlankTotal exceeds the limit of the Long data type.

The code below deals with the error in a more efficient manner: ^[7]

Sub myLongBlankCounter()
'
'
' count the blank cells in a range and deal with the overflow error!
'

'

Dim rng As Range
Set rng = Selection
Dim myBlankTotal As Long

On Error GoTo endProc

myBlankTotal = Application.WorksheetFunction.CountBlank(rng)
MsgBox ("Count of blank cells is " & myBlankTotal)

GoTo skipExit

endProc:
MsgBox "Overflow error encountered - exiting now..."
Exit Sub

skipExit:
End Sub

8. LongLong data type

Computer programming is a progress of continuous improvement: we should always be on the lookout for improvements that can be implemented in our code.

Whilst researching the data types for this post, I discovered that for those of us with 64-bit platforms (operating systems), there is the LongLong data type.

“LongLong (LongLong integer) variables are stored as signed 64-bit (8-byte) numbers ranging in value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.” ^[8]

Here is my (for now) final implementation:

Sub myLongLongBlankCounter()
'
'
' count the blank cells in a range and deal with the overflow error!
'

'

Dim rng As Range
Set rng = Selection
Dim myBlankTotal As LongLong

On Error GoTo endProc

myBlankTotal = Application.WorksheetFunction.CountBlank(rng)
MsgBox ("Count of blank cells is " & myBlankTotal)

GoTo skipExit

endProc:
MsgBox "Overflow error encountered - exiting now..."
Exit Sub

skipExit:
End Sub

If you select the cells of an entire worksheet and run the Macro you will no longer encounter an overflow error. Note that I have left the error handling code in place for completeness.

References:

1. VBA in Excel: check if cell is empty (or multiple are) | EasyTweaks.com (2021). Available at: https://www.easytweaks.com/excel-vba-check-cells-empty-range/ (Accessed: 23 May 2023).
2. Range object (excel) (no date) Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/api/excel.range(object) (Accessed: 23 May 2023).
3. Microsoft (no date) Excel specifications and limits | Microsoft Support. Available at: https://support.microsoft.com/en-gb/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 (Accessed: 7 March 2024).
4. Kathleen Dollard (no date) Long Data Type – Visual Basic, Long Data Type – Visual Basic | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/long-data-type (Accessed: 7 March 2024).
5. Use Worksheet Functions in a Macro – VBA Code Examples (2022) Automate Excel. Available at: https://www.automateexcel.com/vba/worksheet-functions-in-macro/ (Accessed: 10 March 2024).
6. o365devx (no date) Overflow (Error 6), Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/overflow-error-6 (Accessed: 9 March 2024).
7. VBA On Error – Error Handling Best Practices (2021) Automate Excel. Available at: https://www.automateexcel.com/vba/error-handling/ (Accessed: 9 March 2024).
8. o365devx (no date) LongLong data type, Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/longlong-data-type (Accessed: 10 March 2024).