Create a WPS Excel Macro to Filter by Active Cell Value
Question details
The user wants to automate worksheet filtering so that the selected cell determines the filter value for its column.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working in a worksheet and wants a reusable macro that filters the current column by the active cell value.
- Observed behavior
- The filter should update automatically when the macro is run with a cell selected.
Open the workbook in WPS Spreadsheets and make sure the Developer tab is visible. Select a cell in the column that you want to filter before running the macro.
Add a Reusable ActiveCell AutoFilter Macro in WPS Spreadsheets
Use this solution when you want the selected cell to set the filter criterion automatically for its column.
This VBA macro reads ActiveCell.Value and passes it to AutoFilter, so the filter updates each time you run the macro.
Adjust the range and Field number to match your worksheet layout.
In WPS Spreadsheets, click the Developer tab and select View Code, or press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, right-click the workbook project, choose Insert, and then select Module.
Paste the following code into the module: Sub FilterByActiveCell() Dim rng As Range Set rng = ActiveSheet.Range("A1:D100") If ActiveCell.Value <> "" Then rng.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value End If End Sub
Save the file as an Excel Macro-Enabled Workbook (.xlsm) or WPS-compatible macro-enabled format so the VBA code is retained.
Select a cell in the column you want to filter, go to Developer > Macros, choose FilterByActiveCell, and click Run.
Filter by Active Cell with a WPS Spreadsheets VBA Macro
WPS Spreadsheets supports VBA macros, so you can automate active-cell filtering directly in a familiar worksheet environment. The macro reads the selected cell and applies AutoFilter without switching to Microsoft Excel.
- 1. Install WPS Office Free: Download WPS Office Free from the official WPS website and install WPS Spreadsheets.
- 2. Open the workbook: Launch WPS Spreadsheets and open the Excel file that contains the data to filter.
- 3. Create the macro: Press Alt+F11 to open the VBA editor, insert a module, and paste the FilterByActiveCell code.
- 4. Run the macro: Select the cell value to filter by, click Developer > Macros, choose FilterByActiveCell, and select Run.
Frequently Asked Questions
Can I use this active cell filter macro in WPS Spreadsheets?
Yes. WPS Spreadsheets supports VBA macros. Open the VBA editor with Alt+F11, insert a module, and run the macro from Developer > Macros.
How does the macro know which value to use?
The macro uses ActiveCell.Value as the Criteria1 argument and ActiveCell.Column as the AutoFilter Field, so it filters based on the selected cell.
Why does the macro show an error when the active cell is outside the range?
The active cell column must exist inside the specified AutoFilter range. Adjust the range to include that column or use a range that starts in column A.
Which file format should I save to keep the macro?
Save as an Excel Macro-Enabled Workbook (.xlsm) or the WPS macro-enabled format. Ordinary .xlsx files do not retain VBA macros.
How do I enable macros if WPS blocks them?
Check the macro security settings under the Developer tab or Trust Center, and only enable macros for workbooks you trust.
