Bottom line: Learn how to apply filters for blank cells with VBA. Includes examples for filtering blanks and non-blank cells using the AutoFilter method.
Skill level: Intermediate
Download the File
The Excel file that contains the code can be downloaded below. This file contains code for filtering different data types and filter types. Please see my article on The Ultimate Guide to AutoFilters in VBA for more details.
VBA AutoFilters Guide.xlsm (100.5 KB)
Filter for Blank Cells
Filtering for blanks is one very common task we do in Excel. Sometimes that means scrolling down a long list of items in the filter drop-down menu to select the (Blanks) check box at the bottom.
We can also filter for non-blanks cells by unchecking the (Blanks) box.
The following macro contains examples for filtering for blanks and non-blank cells (excluding blanks). Please see my post on The Ultimate Guide to Filters in VBA for more details on how to use the AutoFilter method and its parameters.
VBA Code Samples for Text Filters
The code in the box below can be copy/pasted into the VB Editor.
‘Apply filters to include or exclude blank cells
Dim lo As ListObject
Dim iCol As Long
‘Set reference to the first Table on the sheet
Set lo = Sheet1.ListObjects(1)
‘Set filter field
iCol = lo.ListColumns(“Product”).Index
‘Blank cells – set equal to nothing
lo.Range.AutoFilter Field:=iCol, Criteria1:=”=”
‘Non-blank cells – use NOT operator <>
lo.Range.AutoFilter Field:=iCol, Criteria1:=”<>”
Filters & Data Types
The filter drop-down menu options change based on what type of data is in the column. We have different filters for text, numbers, dates, and colors. This creates A LOT of different combinations of Operators and Criteria for each type of filter.
I created separate posts for each of these filter types. The posts contain explanations and VBA code examples.
- How to Clear Filters with VBA
- How to Filter for Text with VBA
- How to Filter for Numbers with VBA
- How to Filter for Dates with VBA
- How to Filter for Colors & Icons with VBA
The file in the downloads section above contains all of these code samples in one place. You can add it to your Personal Macro Workbook and use the macros in your projects.
Please leave a comment below with any questions or suggestions. Thanks! 🙂