How to Save and Run an Excel Filter Macro

Applying the same complex data filters daily is tedious and prone to manual errors. Learning exactly saving and Run an Excel Filter Macro transforms this repetitive chore into a single-click operation. By capturing your specific filtering criteria—whether you are isolating regional sales, hiding blank rows, or extracting dates within a specific quarter—you can automate the entire sequence. The workflow involves enabling the right tools, recording the precise data manipulation, ensuring the file format preserves the code, and attaching the command to an accessible trigger on your spreadsheet.

Enabling the Developer Tab

Before you can figure out saving and Run an Excel Filter Macro, you must make the macro controls visible in your workspace. The application hides these powerful developer tools by default to prevent accidental code execution by novice users.

1. Click the File tab in the top-left corner and select Options at the bottom of the menu.
2. In the Options dialog box, select Customize Ribbon from the left-hand navigation pane.
3. Look at the right-hand list titled Main Tabs.
4. Check the empty box right next to Developer.
5. Click OK to apply the changes. The Developer tab will now appear on your main ribbon.

Recording Your Filter Criteria

The most straightforward method for creating your automation is using the built-in recorder. This tool tracks your interface clicks and translates them into executable background code, which is the core of saving and Run an Excel Filter Macro effortlessly.

1. Navigate to the newly added Developer tab and click the Record Macro button.
2. In the dialog box, type a recognizable name (such as FilterActiveProjects) ensuring there are no spaces in the name.
3. Choose This Workbook from the Store macro in dropdown menu. Click OK. The recording is now actively capturing your clicks.
4. Select your dataset's top header row. Go to the Data tab and click the Filter icon to apply dropdown arrows to your columns.
5. Click the drop-down arrow on your target column (e.g., Status) and select your specific criteria by checking only the necessary boxes (e.g., "Active"). Click OK to apply the filter.
6. Immediately return to the Developer tab and click Stop Recording.

Your exact filter steps are now captured in the system's memory. A common mistake during this step is leaving the recorder running while doing unrelated cell formatting, which bloats the recorded code.

Preserving Your Automation Work

Standard workbooks strip out all automation scripts upon closing to protect users from malicious code. To ensure you do not lose your work after working to save and run an Excel filter macro, you must change the file extension during the save process.

1. Press F12 on your keyboard, or click File and select Save As.
2. Choose your desired destination folder on your local drive or cloud storage.
3. Click the Save as type dropdown menu located directly below the file name field.
4. Select Excel Macro-Enabled Workbook (*.xlsm) from the provided list.
5. Click Save. If prompted with a security warning about document inspector personal information, click Yes to proceed.

Executing the Automated Filter

Once your criteria and file type are secured, you can execute your saved logic manually through the ribbon menu, or attach it to a clickable shape for faster, one-click access directly on your spreadsheet.

• Through the ribbon menu: Go to the Developer tab, click the Macros button, select your filter macro's name from the dialog list, and click Run. The dataset will instantly filter itself.
• Via a custom shape: Go to the Insert tab, click Illustrations, and choose Shapes. Draw a standard rectangle slightly above your data table. Right-click this new shape and select Assign Macro from the context menu. Choose your filter macro and click OK.

To verify the setup is successful, navigate to the Data tab and clear all current filters from your table. Then, click your custom shape. The specific rows should vanish exactly as they did during your initial recording.

Alternative: Creating a Dynamic Filter with VBA

The standard recorder hardcodes your exact criteria. If you want the filter to change dynamically based on a value typed into a specific cell (like cell F1), a strictly recorded macro will not suffice. This genuinely different approach requires pasting a small script directly into the Visual Basic Editor.

1. Press Alt + F11 to open the Visual Basic Editor window.
2. In the left Project Explorer pane, right-click your current sheet name, choose Insert, and select Module.
3. Paste the necessary script that dynamically references your target cell (e.g., Criteria1:=Range("F1").Value) into the blank white window.
4. Close the editor. You can now execute this dynamic script using the exact same shape assignment method detailed previously.

Executing Filter Automations in WPS Office

While macros and VBA engines are controlled by Microsoft's proprietary backend architecture, many users look for lightweight, cost-effective suites for daily document tasks. WPS Office cannot alter Microsoft's cloud services or internal licensing limits, but if you already understand saving and Run an Excel Filter Macro, WPS Office Spreadsheet can consistent execute your .xlsm files—provided you have the WPS VBA module installed on your system.

1. Open the WPS Office suite and launch the Spreadsheet application.
2. Click Menu in the top-left corner, select Open, and browse your computer for your previously created .xlsm file.
3. Upon opening the file, WPS will display a yellow security banner near the formula bar warning about macros. Click Enable Macros to authorize the background code.
4. Navigate to the Developer tab, which WPS automatically displays when it detects a macro-enabled workbook.
5. Click Macros, highlight your recorded filter script, and click Run.

Any custom buttons or shapes you previously drew and assigned will also remain fully clickable and operational in WPS Office, allowing you to seamlessly process your data without a Microsoft 365 subscription.

Use Word, Excel, and PPT for FREE

Free Download

Frequently Asked Questions

Why does my automation fail when I add new columns?

When you record your interface actions, the system usually hardcodes specific cell ranges (like A1:D100). If you insert a new column into your dataset, the recorded range no longer covers your entire table, causing the filter command to either miss the new data entirely or throw a runtime error. To prevent this, convert your raw dataset into a formal Table (Insert tab > Table) before recording. Tables utilize dynamic ranges that automatically expand when new columns or rows are introduced.

How can I restore the unfiltered view after the macro runs?

No, you cannot use the standard Undo button or the Ctrl+Z shortcut to reverse actions performed by automated background code, which includes your recorded macros. Once the code executes, the previous state of the worksheet is permanently cleared from the system's memory cache. The safest way to revert your view is to navigate to the Data tab and click the Clear button within the Sort & Filter group to reveal all your hidden rows again.

How do I share a macro-enabled workbook safely?

Sharing an .xlsm file requires caution because corporate email clients and network firewalls often flag macro-enabled extensions as potential malware threats. Before sending the file to a colleague, ensure your workbook is saved to a trusted network drive, SharePoint, or a secure cloud folder, and share a direct viewing link rather than an attachment. If you absolutely must email it directly, place the .xlsm file inside a compressed .zip folder to prevent aggressive email security filters from blocking your message in transit.

Will the script work if the worksheet is protected?

By default, applying filters is highly restricted on a protected sheet. If you execute your automation on a locked sheet, the background code will crash and display a "Run-time error 1004." To bypass this limitation, you must explicitly allow sorting and filtering when you establish the lock. Go to the Review tab, click Protect Sheet, and in the detailed permissions list, check the box for Use AutoFilter before typing your password and applying the protection.