Filter data

How to filter data using Excel and VBA methods

METHOD 1. Filter data

EXCEL

Select data > Home tab > Sort & Filter > Filter > Click on filter drop down button > Select what 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.

Click on Sort & Filter and 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 and apply the filter.
7. Click OK.
Note: in this example we are filtering for cells that contain text 'win' in the 'Match Outcome' column.

Select filter dropdown button apply filter and click OK

8. This image represents the results of the filtered data that filters for only 'win' in the 'Match Outcomes' column.

METHOD 1. Filter data using VBA

VBA

Sub Filter_data()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

ws.Range("B2:C9").AutoFilter field:=2, Criteria1:="win"

End Sub

ADJUSTABLE PARAMETERS
Worksheet Names: 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 value ('win') in the VBA code to any value that exists in the filtered range.

METHOD 2. Filter data with cell reference using VBA

VBA

Sub Filter_data()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

ws.Range("B2:C9").AutoFilter field:=(ws.Range("C2").Column - ws.Range("B2").Column) + 1, Criteria1:=ws.Range("E1")

End Sub

PREREQUISITES
Worksheet Names: 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 ('Match Outcome') 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.
Filter Criteria: In this example the filter criteria is sourced from cell ("E1"). Therefore, if using the exact same VBA code cell ("E1") needs to capture the filter criteria.

ADJUSTABLE PARAMETERS
Worksheet Names: 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 cell reference ("E1") to any cell that captures the value that you want to filter for.

Explanation about how to filter data

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to filter data using Excel and VBA methods.

Excel Method: This tutorial provides one Excel method that can be applied to filter data. You initially add the filter option to the selected data and then apply the relevant filter. Using this method you can filter data in seven steps.

VBA Methods: This tutorial provides two VBA methods that can be applied to filter data. 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 and source the filter criteria from a specific cell.