[Excel VBA] AutoFilter Settings to Extract Only Blank Cells [Practical Macro]

2025年12月27日

Overview

In Excel, you can use the AutoFilter feature to extract only blank cells in a specific column, making data checks and bulk entry more efficient.

This article introduces a VBA workflow to instantly filter blank cells and then clear the filter after processing.

Prerequisites

Excel Version: Microsoft 365 or 2016 and later
Data Range: Assumes a table (including headers) is located in the range D4:G50.
Macro Location: Standard Module

Sample Code (VBA)

Sub FilterBlanksOnly()

    Dim ws          As Worksheet
    Dim targetRange As Range      ' Range to filter
    
    Set ws = ActiveSheet
    ' Automatically get the range including headers starting from D4
    Set targetRange = ws.Range("D4").CurrentRegion
    
    '----- Extract only blank cells (e.g., in the 5th column relative to the range) -----
    targetRange.AutoFilter Field:=5, Criteria1:="="
    
    ' (Perform necessary actions on the filtered results here)
    
    '----- Clear the filter criteria (Show All Data) -----
    targetRange.AutoFilter
    
    '----- Turn off the AutoFilter feature itself -----
    If ws.AutoFilterMode Then
        ws.AutoFilterMode = False
    End If

End Sub

Code Explanation

Line 7 (Range("D4").CurrentRegion): Retrieves the entire table area surrounding cell D4. Change the starting cell address to match your actual data.
Line 10 (Field:=5, Criteria1:="="): Field:=5 targets the 5th column within the specified range. Setting Criteria1:="=" filters for empty cells.
Line 15 (targetRange.AutoFilter): calling this without arguments clears the current filter criteria and redisplays all rows.
Lines 18–20 (AutoFilterMode = False): Turns off the filter functionality completely, removing the dropdown arrows from the header row.

Advanced Examples

Goal	Implementation Hint
Filter blanks in multiple columns	Call `AutoFilter` again specifying a different `Field`. This works as an AND condition (narrowing down).
Bulk input into blank cells	You can enter values into only the visible (filtered) cells using `SpecialCells(xlCellTypeBlanks).Value = "N/A"`.
Delete rows with blank cells	By combining `SpecialCells` with `EntireRow.Delete`, you can remove unnecessary rows in one go.

FAQ

Q: Can I use this with Tables (ListObjects)?

A: Yes. You can perform the same operations by replacing the range definition with ListObjects(“TableName”).Range.

Q: How do I extract only non-blank cells?

A: Set the arguments to Criteria1:=”<>” and Operator:=xlAnd.

Summary

By utilizing Criteria1:=”=” in AutoFilter, you can quickly extract only blank cells via VBA.

Please run the sample code to verify that empty cells in the target column are correctly filtered. Mastering this technique, along with post-processing and filter clearing, will further streamline your daily tasks.

よかったらシェアしてね！