How to Filter for Numbers with VBA Macros in Excel

Bottom line: Learn how to apply number filters with VBA.  Includes examples for filtering for a range between two numbers, top/bottom 10, above/below average, etc.

Skill level: Intermediate

VBA AutoFilter Automate Number Filters

 

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)

Number Filters in Excel

When filtering a column that contains numbers, we can pick items from the filter drop-down menu list.

However, it’s usually easier to use the options in the Number Filters sub menu to create a custom filter.  This gives us options for filter criteria that equals, does not equal, great than, less than or equal to, between, top 10, and above/below average.

The following macro contains examples for different types of number filters.   It’s important to note that the Criteria parameter values are wrapped in quotation marks.  The comparison operators are also included inside the quotes.  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.

When applying a filter for a single number we need to use the number formatting that is applied in the column.  This is a weird quirk of VBA that can cause inaccurate results if you don’t know the rule.  There is an example in the code below.

VBA Code Samples for Number Filters

You can copy/paste the code below into the VB Editor.

Sub AutoFilter_Number_Examples()
'Examples for filtering columns with NUMBERS
 
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("Revenue").Index
   
  'Clear Filters
  lo.AutoFilter.ShowAllData

  With lo.Range
  
    'Single number - Use formatting that is visible in
    'filter drop-down menu
    .AutoFilter Field:=iCol, Criteria1:="$2,955.25"
    
    'Not equal to - Does not require number formatting to match
    .AutoFilter Field:=iCol, Criteria1:="<>2955.25"
    
    'Greater than or less than a number
    '(comparison operator < > = before number in Criteria1)
    .AutoFilter Field:=iCol, Criteria1:="<4000"
    
    'Between 2 numbers
    '(greater than or equal to 100 and less than 4000)
    .AutoFilter Field:=iCol, _
                Criteria1:=">=100", _
                Operator:=xlAnd, _
                Criteria2:="<4000"
    
    'Outside range (less than 100 OR greater than 4000)
    .AutoFilter Field:=iCol, _
                Criteria1:="<100", _
                Operator:=xlOr, _
                Criteria2:=">4000"
  
    'Top 10 items (Criteria1 is number of items)
    .AutoFilter Field:=iCol, _
                Criteria1:="10", _
                Operator:=xlTop10Items
    
    'Bottom 5 items (Criteria1 is number of items)
    .AutoFilter Field:=iCol, _
                Criteria1:="5", _
                Operator:=xlBottom10Items
    
    'Top 10 Percent (Criteria1 is number of items)
    .AutoFilter Field:=iCol, _
                Criteria1:="10", _
                Operator:=xlTop10Percent
    
    'Bottom 7 Percent
    .AutoFilter Field:=iCol, _
                Criteria1:="7", _
                Operator:=xlBottom10Percent
    
    'Above Average - Operator:=xlFilterDynamic
    .AutoFilter Field:=iCol, _
                Criteria1:=xlFilterAboveAverage, _
                Operator:=xlFilterDynamic
    
    'Below Average
    .AutoFilter Field:=iCol, _
                Criteria1:=xlFilterBelowAverage, _
                Operator:=xlFilterDynamic
    
  End With
  
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 0 comments

Leave a Reply: