VBA & Macro Problems

Excel VBA: Filter Table Data by a Number in Another Cell

Phi Hung Vo Nov 12, 2026 869 views

Question details

The user needs to automatically filter table or worksheet data according to a numeric value stored in another cell.

Excel VBA: Filter Table Data by a Number in Another Cell

Product: Microsoft Excel VBA
Device & OS: not provided
Scenario: A worksheet contains a numeric criterion, such as a value in E2, and the user wants the table to show only rows meeting that criterion when the cell changes.
Observed behavior: The data should refresh its filter automatically after the numeric criterion cell is edited.

Before you start

Confirm the criterion cell, table name, and filter column number before adding the event macro. Save the workbook as a macro-enabled .xlsm file so the Worksheet_Change code can run.

Solution 1Recommended

Use a Worksheet_Change macro to filter the Excel table automatically

Use this solution when the filter must refresh immediately after a user changes the numeric value in a specific cell.

This event procedure belongs in the worksheet module, not in a standard VBA module. The example filters Table1 by field 3 and keeps rows whose values are less than or equal to the value in E2.

Open the worksheet code window

Press Alt+F11 to open the Visual Basic Editor. In the Project Explorer, double-click the worksheet that contains the criterion cell and data table.

Insert the change event code

Paste this code into the worksheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("E2")) Is Nothing Then Exit Sub If Target.CountLarge > 1 Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False Me.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:="<=" & Me.Range("E2").Value CleanUp: Application.EnableEvents = True End Sub

Match the references to your worksheet

Replace E2 with the actual criterion cell, Table1 with the table name shown under Table Design, and Field:=3 with the relative column number that should be filtered.

Test the automatic filter

Close the Visual Basic Editor, enter a new number in the criterion cell, and verify that the table displays only rows that meet the less-than-or-equal-to condition.

Prevent events from staying disabled: The CleanUp section restores Application.EnableEvents even if an error occurs, allowing later worksheet events to run normally.

Solution 2

Use the FILTER function for a formula-based result

Use this option when you need a separate dynamic result area and do not need to change the original table's filter state.

WPS Spreadsheet solution

Automate Numeric Table Filtering with WPS Spreadsheet

WPS Spreadsheet can help you build a worksheet-based filtering workflow using compatible VBA macros or a FILTER formula. Set the criterion cell, connect it to the table or output range, and update the results whenever the value changes.

1. Open the workbook: Launch WPS Office, open the workbook in WPS Spreadsheet, and confirm the criterion cell and data range.
2. Add the worksheet event macro: Open the VBA editor from the developer tools, select the target worksheet module, and paste the Worksheet_Change code with your cell, table, and field references.
3. Use a formula alternative: If macros are unnecessary, select an empty output cell and enter a FILTER formula that compares the data column with the numeric criterion cell.
4. Save and test the result: Save the workbook in XLSM format when using VBA, change the criterion value, and verify that the table or formula output refreshes correctly.

Automatically refresh filtered results when the numeric criterion changesSupports table-based filtering and formula-driven dynamic resultsProvides a familiar spreadsheet interface for editing VBA and formulasCompatible with Microsoft Excel XLSM and XLSX file formats

microsoft office alternative - wps office

Frequently Asked Questions

Where should the Worksheet_Change VBA code be pasted?

Paste it into the code module for the worksheet containing the criterion cell. In the Visual Basic Editor, press Alt+F11, expand the workbook, and double-click the target sheet; do not paste this event procedure into a standard module.

How do I filter a normal range instead of an Excel table?

Replace Me.ListObjects("Table1").Range with a range expression such as Me.Range("A2").CurrentRegion, then keep the Field number aligned with the column position inside that range. For example, use Field:=3 to filter the third column.

Why does the macro use Application.EnableEvents = False?

The setting prevents the macro from triggering another Worksheet_Change event while it applies the filter. The CleanUp section must restore it to True so future worksheet events continue to work.

Can I filter for values greater than or equal to the number in E2?

Yes. Change Criteria1:="<=" & Me.Range("E2").Value to Criteria1:=">=" & Me.Range("E2").Value. You can also use other AutoFilter criteria supported by the spreadsheet application.

Can I use a formula instead of VBA for this task?

Yes. In a separate output area, use a formula such as =FILTER(A2:C100,C2:C100<=$E$2,"No matching rows"). The formula updates the returned results but does not hide rows in the original table.

Excel & Data Problems