VBA & Macro Problems

How to Create a VBA Macro to Filter Excel Data by Birth Month and City

Khadija Khan Sep 28, 2026 871 views

Question details

The user needs to create an Excel VBA macro that filters a large table by birth month and city without using Advanced Filter, copies specific columns to a new area, and calculates metrics like match count and average salary.

How to Filter Excel Data by Birth Month and City Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Filtering a large dataset to extract specific records to a new region and calculating summary statistics automatically via a graphic object click.
Observed behavior: The user requires a structured macro script capable of clearing old results, handling cases with no matches, and processing criteria dynamically based on values or cell formatting.

Before you start

Before writing your macro, ensure the Developer tab is enabled in your ribbon and that your spreadsheet is saved as a Macro-Enabled Workbook (.xlsm) to prevent losing your code.

Solution 1

Clarify Data Logic and Formatting Requirements

Complex VBA macros depend heavily on consistent data structure. Verifying your filtering logic is a critical first step before writing the code.

Because the macro needs to handle multiple criteria and potentially text formatting, determining how the criteria are identified will dictate the VBA method you use.

Determine City Criteria Logic

Decide whether the macro should filter the city based on exact text values or rely on cell formatting (such as identifying bold text). AutoFilter works perfectly for values, but formatting requires a VBA loop.

Map the Output Columns

Identify exactly which columns from the source table need to be copied. Note their column index numbers to reference them correctly in your script.

Define the Expected Output

Manually apply standard Excel filters to ensure your intended logic yields the expected results. This creates a baseline sample to test your macro against.

Solution 2Recommended

Use VBA AutoFilter to Extract Data and Calculate Metrics

Applying the built-in AutoFilter method within VBA is an efficient way to filter tables by multiple criteria, copy the visible results, and compute statistics.

Powerful VBA Support

Use WPS Spreadsheet to Build and Run Complex Macros

WPS Office offers robust built-in support for VBA macros, allowing you to seamlessly automate complex data filtering, extraction, and calculations. Its interface is highly compatible with Microsoft Excel, making macro migration effortless.

1. Open your Workbook: Launch WPS Spreadsheet and open your Macro-Enabled Workbook containing the raw data.
2. Access the VBA Editor: Navigate to the Developer tab on the ribbon and click on 'Visual Basic' to open the code editor.
3. Write and Execute: Paste or write your custom AutoFilter macro, save the script, and assign it to a shape or button in your worksheet to run it instantly.

Highly compatible with Microsoft Excel (.xlsm) formats and VBA syntaxRobust built-in VBA editor for writing, debugging, and testing macrosLightweight application that processes large filtered datasets quicklyFree to download and provides a familiar user interface

Frequently Asked Questions

How do I filter dates by birth month in VBA?

To filter specifically by month, it is often easiest to create a helper column in your dataset using the `=MONTH()` function. You can then use the standard `AutoFilter` method in VBA to filter that helper column for a specific month number (e.g., 1 for January).

Can a VBA macro filter data based on cell formatting like bold text?

The standard AutoFilter method does not support filtering directly by font weight. To filter by bold text, you must write a VBA loop to inspect `Cell.Font.Bold = True` for each row, or use a custom VBA function to output a True/False value into a helper column, which can then be filtered.

How do I assign my VBA macro to a clickable shape?

Go to the Insert tab and select a Shape. Draw it on your worksheet, right-click the shape, and select 'Assign Macro' from the context menu. Choose your specific filtering macro from the list and click OK. Clicking the shape will now trigger your script.

Excel & Data Problems