Filter between two numbers
How to filter between two numbers using Excel and VBA methods
Select data > Home tab > Sort & Filter > Filter > Number Filters > Between > Enter the numbers between which to filter for > OK
| 1. Select the range that captures the data you want to filter, including headers Note: in this example we are selecting range (B2:C9). |
 |
| 2. Select the Home tab. |  |
| 3. Click on Sort & Filter in the Editing group. 4. Click on Filter. |
 |
| 5. The top row of the selected data will now have drop down buttons from which you can apply your filter. |  |
| 6. Click on the filter drop down button. 7. Select Number Filter. 8. Click on Between. Note: in this example we are applying a filter against the Quantity column. |
 |
| 9. Select is greater than or equal to from the first dropdown menu and select is less than or equal to from the second dropdown menu. Note: make sure that the And radio button is selected. 10. Enter the lower of the two numbers that you want to filter between in the input box relating to is greater than or equal to. Note: in this example we the lower of the two numbers is 200. 11. Enter the higher of the two numbers that you want to filter between in the input box relating to is less than or equal to. Note: in this example we the higher of the two numbers is 700. 12. Click OK. |
 |
| 13. This image represents the results of the filtered data that filters for 200 and 700 in the 'Quantity' column. |  |
METHOD 1. Filter between two numbers using VBA
VBA
Sub Filter_between_two_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Range("B2:C9").AutoFilter Field:=2, Criteria1:=">=200", Operator:=xlAnd, Criteria2:="<=700"
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Sheet1.
Data Range: In this example the data that is being filtered is captured in range ("B2:C9"). Therefore, if using the exact same VBA code, the VBA code will apply a filter to this range.
Filter Field: In this example we are filtering against the second column ('Quantity') by assigning a value of 2 against 'filed', in the VBA code.
Worksheet Name: Have a worksheet named Sheet1.
Data Range: In this example the data that is being filtered is captured in range ("B2:C9"). Therefore, if using the exact same VBA code, the VBA code will apply a filter to this range.
Filter Field: In this example we are filtering against the second column ('Quantity') by assigning a value of 2 against 'filed', in the VBA code.
ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet that captures the data that you want to filter by changing the Sheet1 worksheet name.
Data Range: Select the range that captures the data that you want to filter by changing the range reference ("B2:C9").
Filter Field: Select the filter filed by changing the filed number (2) in the VBA code to any number that is withing the applied filter.
Filter Criteria: Select the filter criteria by changing the criteria values 200 and 700 in the VBA code.
Worksheet Selection: Select the worksheet that captures the data that you want to filter by changing the Sheet1 worksheet name.
Data Range: Select the range that captures the data that you want to filter by changing the range reference ("B2:C9").
Filter Field: Select the filter filed by changing the filed number (2) in the VBA code to any number that is withing the applied filter.
Filter Criteria: Select the filter criteria by changing the criteria values 200 and 700 in the VBA code.
METHOD 2. Filter between two numbers with cell reference using VBA
VBA
Sub Filter_between_two_numbers()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.Range("B2:C9").AutoFilter Field:=(ws.Range("C2").Column - ws.Range("B2").Column) + 1, Criteria1:=">=200", Operator:=xlAnd, Criteria2:="<=700"
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Sheet1.
Data Range: In this example the data that is being filtered is captured in range ("B2:C9"). Therefore, if using the exact same VBA code, the VBA code will apply a filter to this range.
Filter Field: In this example we are filtering against the second column ('Quantity') by applying a calculation where we take the column number of the filed that we want to filter by and subtract the column number of the first column where we have applied a filter and add a value of 1.
Worksheet Name: Have a worksheet named Sheet1.
Data Range: In this example the data that is being filtered is captured in range ("B2:C9"). Therefore, if using the exact same VBA code, the VBA code will apply a filter to this range.
Filter Field: In this example we are filtering against the second column ('Quantity') by applying a calculation where we take the column number of the filed that we want to filter by and subtract the column number of the first column where we have applied a filter and add a value of 1.
ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet that captures the data that you want to filter by changing the Sheet1 worksheet name.
Data Range: Select the range that captures the data that you want to filter by changing the range reference ("B2:C9").
Filter Field: Select the filter filed by changing cell references that relate to the field that you want to apply the filter and the first column of the filtered data. In this example, the field that we are filtering against in the second field, with the heading captured in cell ("C2")./span>
Filter Criteria: Select the filter criteria by changing the criteria values 200 and 700 in the VBA code.
Worksheet Selection: Select the worksheet that captures the data that you want to filter by changing the Sheet1 worksheet name.
Data Range: Select the range that captures the data that you want to filter by changing the range reference ("B2:C9").
Filter Field: Select the filter filed by changing cell references that relate to the field that you want to apply the filter and the first column of the filtered data. In this example, the field that we are filtering against in the second field, with the heading captured in cell ("C2")./span>
Filter Criteria: Select the filter criteria by changing the criteria values 200 and 700 in the VBA code.
EXPLANATION
This tutorial explains and provides step by step instructions on how to filter between two numbers using Excel and VBA methods.
This tutorial explains and provides step by step instructions on how to filter between two numbers using Excel and VBA methods.
Excel Method: This tutorial provides one Excel method that can be applied to filter between two numbers. You initially add the filter option to the selected data and then apply the relevant filter. Using this method you can filter between two numbers in 12 steps.
VBA Methods: This tutorial provides two VBA methods that can be applied to filter between two numbers. The first method applies the filter filed and criteria directly into the VBA code. The second method applies a more dynamic approach where it references to the relevant cells to calculate the filter field.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Sort data alphabetically (A-Z) in a column | How to sort data in an alphabetical order (A to Z) in a column using Excel and VBA methods |