How to Find Blank Cells Using VBA in Excel: 6 Methods

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

Step 1: Open Microsoft Visual Basic; 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.

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.

➤ Run the previous macro and see whether it detects the blank cell. See from the screenshot below that the macro returns FALSE because it considers spaces as an entry.

Ignoring Spaces

To overcome the ignoring spaces, 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 below.

Method 2 – Find First 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.

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

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: 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.

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

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: To run the macro press F5. Pressing F5 1^st shows the blank cells count in a message box. Click OK.

➤ Clicking OK takes you to the Microsoft Visual Basic window. 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

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

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 OK.

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

➤ Clicking OK, the macro displays the last location of blank cells. You can validate it by looking at the dataset that the last blank cell is F15.

It’s 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.

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: Press F5 key to run the macro. The macro takes you to the worksheet. If the range contains blank cells, the macro displays a warning, as shown in the image.

Download Excel Workbook

Find Blank Cells Using VBA.xlsm

Get FREE Advanced Excel Exercises with Solutions!