How to Find Blank Cells Using VBA in Excel (6 Methods)

In general, Excel workbooks contain numerous cells. And numerous cells are kept blank for multiple reasons. There are multiple ways to find blank cells using VBA macros in Excel.

Let’s say, we have a dataset where sold Products are displayed with Order Date, Quantity, Unit Price, and Total Price. However, some of the products are unsold on certain dates. Therefore, the Quantity and Total Price cells of respective products remain blank. We want to find the blank cells existing in the dataset using VBA macros.

In this article, we demonstrate different variants of VBA macros to find blank cells in Excel.

⧭ Opening Microsoft Visual Basic and Inserting Code in the Module

Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.

🔄 Opening Microsoft Visual Basic: There are mainly 3 ways to open Microsoft Visual Basic window.

🔼 Using Keyboard Shortcuts

Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

In an Excel worksheet, Go to the Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

🔄 Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window,

🔼 After opening the Microsoft Visual Basic window, Select a Worksheet > Right-Click on it > Select Insert (from the Context Menu) > then Choose Module.

🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

How to Find Blank Cells Using VBA in Excel: 6 Easy Ways

Method 1: Check Whether a Specific Cell Is Blank or Not

From the dataset (shown below), we know that our worksheet contains multiple blank cells. In the beginning, we want to check whether a specific cell is blank or not. As a result, we use the VBA IsEmpty function to display TRUE or FALSE in a message box.

Step 1: Open Microsoft Visual Basic then insert a Module using the instruction section. Paste the following macro in any Module.

Sub Check_SpecificCell_ForBlanks()
MsgBox IsEmpty(Range("D6"))
End Sub

➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – execute the VBA IsEmpty function for the specific cell D6 in the active worksheet. You can enter any cell reference you want.

Step 2: Press F5 to run the macro. The macro takes you to the active worksheet and displays a message box. The message box displays TRUE if the specific cell is blank or False otherwise.

Since you can see the D6 cell in the dataset is blank, IsEmpty returns TRUE.

🔁 Issues with Ignoring Spaces

The previous macro takes spaces as an entry. Therefore, returns FALSE if there are any spaces in the respective cell.

➤ Enter spaces in any blank cell (i.e., D6) as shown in the following picture.

➤ Then run the previous macro and see whether it detects the blank cell or not. You can see from the below screenshot that the macro returns FALSE. Because it considers spaces as an entry.

🔁 Ignoring Spaces

To overcome the ignoring spaces issue, use the following macro.

Sub Check_SpecificCell_forBlanks()
If Trim(Cells(6, 4)) = "" Then
MsgBox "The Cell is Blank"
Else
MsgBox "The Cell is not blank"
End If
End Sub

➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – assign the specific cell by VBA CELL and the VBA TRIM function clears any spaces preexisting in the specific cell. The whole thing is conditional by the VBA IF Statement.

3 – display an assigned text in case of blank cell or otherwise.

➤ Use the F5 key to run the macro. The macro ignores spaces in the specific cell and returns the assigned text as depicted in the below picture.

Method 2: Find First Blank Cell in a Specific Column

In Method 1, we checked whether a specific cell is blank or not. In this method, we find the 1^st blank cell in a specific column.

Our dataset has 5 columns and 2 of them contain blanks. The 4 and 6 number columns contain blank cells. Suppose we want to find the 1^st blank cell from column 4 (i.e., Quantity column).

Step 1: Repeat the instruction section to open and insert Module in Microsoft Visual Basic.

Sub FirstBlank_InColumn()
Dim wSheet As Worksheet
Set wSheet = ActiveSheet
For Each i In wSheet.Columns(4).Cells
If IsEmpty(i) = True Then i.Select: Exit For
Next i
End Sub

➤ From the above image, in the sections,

1 – begin the macro code declaring the VBA Macro Code’s Sub name.

2 – declare the variables as Worksheet and assign the Worksheet to an Activesheet.

3 – create a loop where the macro finds the 1^st blank in column 4 using the VBA IsEmpty function.

Step 2: Hit F5 to run the macro. Afterward, return to the active sheet. You see the macro places the green rectangular indicating the 1^st blank cell of column 4.

Method 3: Finding Color-Formatted Blank Cells Using VBA in Excel

Now, we want to take the macros a step further. We want to highlight the blank cells within a range of a specific sheet. In order to highlight the blank cells, we apply color formatting on them.

Before executing the macro, the depiction of our dataset is similar to the below image.

Step 1: Type the below macro code in the Module (inserted by following the instruction section).

Sub ColorFormat_BlankCells()
Dim i As Long
Dim P As Long
Dim wrkRng As Range
Dim wrkCell As Range
Set wrkRng = Sheet1.Range("B5:F17")
For Each wrkCell In wrkRng
P = P + 1
If IsEmpty(wrkCell) Then
wrkCell.Interior.Color = RGB(255, 87, 87)
i = i + 1
End If
Next wrkCell
MsgBox _
"There are total " & i & " blank cell(s) out of " & P & "."
End Sub

➤ The code’s sections,

1 – initiate the macro procedure declaring the Sub name.

2 – declare the variables as Long and Range.

3 – assign the wrkRng variable to a particular worksheet (i.e., Sheet1).

4 – create a loop to check each cell within wrkRng using VBA IsEmpty function. And apply a specific color using VBA Interior.Color Statement to format blank cells.

5– display the total number of blank cells with the total cell count in a message box.

Step 2: To run the macro, Press F5. In a moment, the macro takes you back to the worksheet, you see the blank cell and total cell count along with the color formatting.

By changing the color codes inside the VBA RGB function, you can apply any color for blank cell formatting.

Method 4: Find and Modify Blank Cells Using VBA

What if we want to insert a text to indicate it as a blank cell? To do so, we first find blank cells using the VBA Selection.SpecialCells statement, then, insert specific text by the VBA Cells.Value statement.

Step 1: Select the entire (B5:F17) range as shown in the picture below.

Step 2: Write the subsequent macro in the Microsoft Visual Basic Module.

Sub Find_BlankCells_Modify()
Dim BlnkCells As Range
If TypeName(Selection) <> "Range" Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next
Set BlnkCells = Selection.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not BlnkCells Is Nothing Then
MsgBox "There are " & BlnkCells.Count & " within cell selection."
BlnkCells.Value = "Blank_Cell"
End If
End Sub

➤ From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variable as Range.

3 – ensure a range selection otherwise exit the macro.

4 – perform optimization.

5 – assign the BlnkCells variable to the selected range.

6 – VBA IF performs a condition to insert text in the blanks.

7 – message box displays the count of blank cells.

8 – assign a specific text (i.e., Blank_Cell) value to insert in the blanks.

Step 3: In order to run the macro press F5. Pressing F5 1^st shows the blank cells count in a message box. Click on OK.

➤ Clicking OK takes you to the Microsoft Visual Basic window. Again, return to the worksheet you see the specific text (i.e., Blank_Cell) is inserted in the blanks.

Method 5: Find Exact Location of Blank Cells

Some real-life scenarios may lead to finding the exact location of blank cells from a selection in the worksheet. For that reason, we want to find each and every blank cell with its exact location. A VBA Loop with an assigned field title can achieve the job.

Step 1: Select the range you want the exact location of blank cells from.

Step 2: Use the following macro code in any Module of any Microsoft Visual Basic window.

Sub Find_ExactBlankCells()
Dim Rng As Range
Dim WrkRng As Range
On Error Resume Next
xTitleId = "myRng"
Set WrkRng = Application.Selection
Set WrkRng = Application.InputBox("Range", xTitleId, WrkRng.Address, Type:=8)
For Each Rng In WrkRng
If Rng.Value = "" Then
MsgBox "Existing Blank Cell Location " & Rng.Address
End If
Next
End Sub

➤ The above image has sections,

1 – take forward the macro by setting the Sub name.

2 – declare the variables as Range.

3 – set a title. You can set a title of your own.

4 – assign the wrkRng variable to the selection and application input.

5 – execute a loop to display each blank cell with its exact location in the worksheet.

Step 3: To run the macro press F5. The macro displays a dialog box (i.e., myRng) to insert a selection for your range. After assigning the range as you select the range at the beginning of this process, click on OK.

➤ Assigning the range brings the 1^st blank cell location in a message box as shown in the following image. Click on OK. Clicking OK sequentially displays blank cell locations until the macro finishes showing them.

➤ At the end of clicking OK, the macro displays the last location of blank cells. And you can validate it by looking at the dataset that the last blank cell is F15.

It’s quite a useful method to identify blank cells with their exact location when there are numerous cells to deal with.

Method 6: Show Warning In Case of Blank Cells Present in the Range

There can be situations where we want to restrict users from filling each cell in a dataset. In that case, we don’t want any blank cells in the range. A variant of VBA macro can be used to do so.

We have the same dataset we use for other methods as shown below.

Step 1: Use the following macro in any Module.

Sub Warning_BlankCell_Presence()
Range("B5:F17").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "wrkRng"
ActiveSheet.Name = "wrkSheet"
If Range("wrkRng").SpecialCells(xlCellTypeBlanks).Count > 0 Then
MsgBox ("Please Ensure no Blank Cells."), vbCritical, "Blank cell Exist!"
Cancel = True
End If
End Sub

➤ The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – assign the range (i.e., B5:F17) to select.

3 – insert a Worksheet name (i.e., wrkSheet) and range name (i.e., wrkRng) for the selection.

4 – VBA IF function imposes a condition to display a warning saying the range contains Blank Cell.

Step 2: Push the F5 key to run the macro. As a result, the macro takes you to the worksheet. If the range contains blank cells, the macro displays a warning as shown in the latter image.

Download Excel Workbook

Find Blank Cells Using VBA.xlsm

Conclusion

In this article, we’ve demonstrated multiple variants of VBA macros to find blank cells in Excel. Each macro we demonstrated has its own convenience depending on the data type. Hope these above-mentioned methods serve your purpose. Comment, if you have further inquiries or have anything to add.

Get FREE Advanced Excel Exercises with Solutions!