3

How to Filter for Blanks and Non-Blank Cells with VBA Macros

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

How to Filter for Blank Cells in VBA AutoFilter Method

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.

VBA AutoFilter Filter for Blank Cells

We can also filter for non-blanks cells by unchecking the (Blanks) box.

VBA AutoFilter Filter for Non-Blank Cells

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.
Sub Blank_Cells_Filter()
‘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:=”<>”

End Sub

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.

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 3 comments
Alex - August 17, 2018

Hi, Jon!
Is it possible to write a macro, which will add blanks to already applied filters? I have a filtered range (not a table) and when applying a filter I need manually add blanks to a filter to add new data inside that range. So I would like to have a button, which will add “(Blanks)” to any filtered item.

Reply
Maria - July 23, 2018

Hi Jon, this is very helpful, thanks!
I have question: how to output the range of cell value after filter the data. for example: after filter the blank cells, I got a range A82:A85 have blank cell, cell value is 80 for A82 which is start number of blank cell value, and 83 for A85 which is end number of blank cell value.(the number between 80 and 83 are continual , like 80,81,82,83) I want to output both number (80 and 83) to another sheet with column title “start number blank cell” and “end number blank cell”. Please advise, and really appreciated your help! ~irea

Reply
    Jon Acampora - July 24, 2018

    Hey Maria,
    Great question!
    There are a few ways to go about this. One easy way is to copy and paste the visible cells only. We can use the SpecialCells method for this to reference the visible (filtered) cells only.

    The following line will copy the visible cells only in the filtered range. You can change the range reference “A2:A85” to your ENTIRE filtered range, NOT just the visible rows after the filter is applied.

    Sheet1.Range("A2:A85").SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A1")

    I hope that helps.

    Reply

Leave a Reply: