VBA & Macro Problems

How to Number Only Visible Rows After Filtering in Excel and VBA

John Wilson Oct 23, 2026 869 views

Question details

The user wants to apply sequential numbering to a list of data so that the sequence skips hidden rows and only numbers the visible rows after a filter is applied.

How to Number Only Visible Rows After Filtering in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering or manually hiding data in a spreadsheet while needing to maintain a continuous, unbroken numbered list for the remaining visible rows.
Observed behavior: Standard drag-and-fill numbering or the ROW() function includes hidden rows, resulting in gaps in the sequential numbering of the visible data.

Before you start

Ensure your data range is properly defined and back up your workbook before running any VBA macros to prevent accidental data overwrites.

Solution 1Recommended

Use the SUBTOTAL Formula to Number Visible Rows

The SUBTOTAL function dynamically recalculates based on visible cells, making it the most efficient way to number rows after filtering.

Using a formula is generally preferred over VBA for this task because it updates instantly whenever you change your filter criteria. By using the COUNTA parameter within the SUBTOTAL function, Excel will count only the cells that remain visible.

Select the Starting Cell

Click on the first cell in the column where you want the sequential numbering to begin (for example, E16).

Enter the SUBTOTAL Formula

Type the formula =SUBTOTAL(3, $A$16:$A16) into the formula bar. Replace 'A16' with the first cell of a column that contains data and has no empty cells.

Apply to the Entire Column

Press Enter to confirm the formula. Click the cell again, grab the fill handle in the bottom-right corner, and drag it down to apply the formula to the remaining rows.

Use the SUBTOTAL Formula to Number Visible Rows

Understanding the Formula: The '3' in the formula tells Excel to use the COUNTA function (counting non-empty cells). The absolute reference ($A$16) anchors the start of the range, creating an expanding range as you drag the formula down.

Solution 2

Apply a VBA Macro to Assign Numbers to Visible Rows

Write a VBA script to iterate through a specific range and increment a counter only for rows that are not hidden.

Advanced Spreadsheet Tools

Number Visible Rows Effortlessly in WPS Spreadsheet

WPS Office offers a powerful, highly compatible Spreadsheet application that fully supports advanced formulas like SUBTOTAL and VBA macros, allowing you to easily manage dynamic data without layout breaking.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing the dataset you wish to filter.
2. Apply Filters: Navigate to the Data tab on the top ribbon and click the Filter button to add dropdown arrows to your headers.
3. Insert Formula: Use the =SUBTOTAL(3, $A$2:$A2) formula in your numbering column and use the fill handle to drag it down through the rest of your data.
4. Run Custom Macros: If you prefer automation, go to the Developer tab to seamlessly insert and run your VBA visibility check scripts.

Fully compatible with Microsoft Excel formulas and VBA macros.Lightweight software that processes large datasets quickly without lag.Free and intuitive interface for seamless navigation and immediate productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard Excel numbering include hidden rows?

Standard numbering techniques, such as dragging the fill handle or using the ROW() function, reference the absolute position of the cells. These methods do not evaluate whether a row is visible or hidden, which leads to numbering gaps when a filter is applied.

Does the SUBTOTAL method work if I manually hide rows instead of filtering?

If you manually hide rows instead of using a filter, you should use '103' instead of '3' as the first argument in the SUBTOTAL formula (e.g., =SUBTOTAL(103, $A$16:$A16)). The '103' option specifically ignores manually hidden rows, whereas '3' only ignores rows hidden by an active filter.

Can I use the AGGREGATE function to number visible rows?

Yes, the AGGREGATE function can also ignore hidden rows by using option 5. For example, the formula =AGGREGATE(3, 5, $A$2:$A2) works similarly to SUBTOTAL and provides more advanced error-handling capabilities if your dataset contains errors.

How do I ensure the VBA macro updates when I change filters?

Unlike formulas, VBA macros do not automatically trigger upon filtering. To update the numbers dynamically, you would need to run the macro again manually, assign the macro to a custom button on the worksheet, or utilize a Worksheet_Calculate event to trigger it automatically.

Excel & Data Problems