VBA & Macro Problems

How to Create an Excel VBA Macro to Filter and Summarize a Table

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to create an Excel VBA macro that filters a large table of people by birth month and city, copies specific columns to a new location, and calculates the total record count and average salary.

Product: Excel
Device & OS: not provided
Scenario: Automating data extraction and calculation from a primary table using a custom VBA script triggered by a graphic object, without relying on the Advanced Filter feature.
Observed behavior: The macro should successfully clear previous output (retaining headers), handle scenarios where no matches are found, and output the accurately filtered rows alongside the summarized metrics.

Before you start

Before writing your macro, confirm your workbook's exact structure, including table names, column headers, target date formats, and the exact destination range for the output data.

Solution 1Recommended

Write a Custom VBA Macro using AutoFilter

Use standard VBA AutoFilter methods to process the data array, copy visible cells to a destination range, and calculate the required summaries dynamically.

Since the requirement strictly avoids the Advanced Filter feature, standard AutoFilter combined with SpecialCells is the most efficient way to extract data and handle conditions like zero matches.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic Editor, then click 'Insert' > 'Module' to create a new script area.

Clear Previous Results

In your macro, define your destination range below the headers and clear old data using a command like `Range("OutputData").ClearContents`.

Apply AutoFilter Criteria

Apply the `.AutoFilter` method to your source data range, specifying the field numbers for 'Birth Month' and 'City', and setting the criteria to the desired values.

Handle No Matches

Count the visible rows after filtering. If the count is 1 (only headers are visible), display an alert using `MsgBox "No matching records found"` and exit the sub.

Copy Visible Cells

If records match, use `Range.SpecialCells(xlCellTypeVisible).Copy` to transfer only the filtered data to your designated output location.

Calculate Summaries

Use `WorksheetFunction.CountA` on the copied names for the record count, and `WorksheetFunction.Average` on the salary column to generate your required summary metrics.

Triggering Macro from a Graphic Object: To run this macro from a worksheet graphic, go to Insert > Shapes, draw a shape, right-click it, select 'Assign Macro', and choose your newly created script.

Advanced Data Processing in WPS

Filter and Summarize Data Automations with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to automate complex filtering, copying, and summarization tasks just like Microsoft Excel. Execute VBA scripts seamlessly in a lightweight, user-friendly environment.

1. Enable Developer Tools: Open WPS Spreadsheet, navigate to the menu settings, and ensure the Developer tab is enabled so you can access the Macro and VBA features.
2. Write the Filter Macro: Click 'Visual Basic' in the Developer tab, insert a module, and paste your VBA AutoFilter and summary calculations script.
3. Assign and Execute: Insert a graphic object or shape into your spreadsheet, right-click to choose 'Assign Macro', and automate your summarization with a single click.

High compatibility with Microsoft Excel VBA scripts and AutoFilter methods.Execute complex WorksheetFunction calculations automatically via code.Assign macros to shapes, buttons, and graphics easily.Fully compatible with Excel macro-enabled workbooks (.xlsm and .xlsb formats).

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro copying hidden or filtered-out rows?

If your macro copies rows that were meant to be hidden by the filter, ensure you are appending `.SpecialCells(xlCellTypeVisible)` to your range before executing the `.Copy` command.

Can I filter dates by month directly in VBA AutoFilter?

Filtering dates purely by month in VBA can be complicated due to regional date formats. The most reliable method is to use a helper column in your table with the formula `=MONTH()` and filter on that column, or set the filter criteria using a date range (>= StartDate and <= EndDate).

How do I clear previous results in VBA without deleting the header row?

Identify the data area directly below your headers dynamically or via named ranges (e.g., `Range("A2:D100")`), and use `.ClearContents` instead of `.Clear` to remove the values while keeping formatting and headers intact.

How do I calculate an average in VBA and output it to a cell?

You can utilize Excel's built-in functions inside VBA by calling `Application.WorksheetFunction.Average(Range("YourRange"))` and assigning that resulting value to your target output cell.

Excel & Data Problems