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
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.
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.
- How to Clear Filters with VBA
- How to Filter for Blank & Non-Blank Cells
- How to Filter for Text 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! 🙂
Hello,
I am trying to figure out how to combine functions to create a formula to solve the following issue:
I was given a stack of receipts that were supposed to correlate with a check amount used to pay for the expenses but the stack of receipts ends up totaling 700+ over the check amount. So having everything logged into excel I need to create a formula that will search the long list of dollar amount cells and select the cells that add up to the total dollar amount on the check. This will help me figure out which receipts don’t belong in the stack.
Would anyone know how to write this formula and which functions to combine?
I was thinking v-lookup with absolute value but haven’t been able to make it work….
Thank you all for your help!
Sincerely,
Nicole
I use crystal reports to pull values. Then the values are manually entered into a master sheet and any cell that contains a value +/-5 without a comment must be highlighted. Another report has the comments which must be manually entered. If it has a comment it does not need to be flagged. Can a macro be used with this criteria? Rather than create a master sheet, can I merge the crystal report into the master sheet and have it unhighlight cells that now meet the criteria? (As the month goes on comments will be back entered and the cells can be unflagged.) I also need new values to replace old values if there has been a change, which is common. I need to run it daily.
Entering one day’s comments on each if the cells alone takes 30 minutes!
Thanks!
How to modify this
‘Between 2 numbers
‘(greater than or equal to 100 and less than 4000)
.AutoFilter Field:=iCol, _
Criteria1:=”>=100″, _
Operator:=xlAnd, _
Criteria2:=”<4000"
with criteria1 and 2 is based on cell value
It was Very useful . Thanks a lot 🙂
Once filtered, the filtered values are not checked when you click on the filter dropdown box. Is there any way to force these to be checked based on the criteria? For example “>0”, all values greater than zero would be checked.
What about filtering by an array of numbers? This seems to be much more difficult than an array of strings.
Even converting the array of numbers to a string is not successful.
Thanks for the concise tutorial.
My challenge is that the colunm i want to apply filter to has phone numbers that always start with a zero(0) digit, example 08036xxxx .
I use input box to receive the phone numbers, but when the code runs, the filter selection is aplied and only empty cells are selected. However, when I manually embed the number in the code (not using the input box), the code works fine.
Could you help,please.