Numbering Filtered Rows

Greg knows he can use filtering to display just a subset of rows in a worksheet. He would like, in an unused column, to sequentially number the rows that are visible after filtering. He can use AutoFill to number unfiltered rows, but he wants to number just the visible, filtered rows.

The simple, traditional way to do this is to add a formula to the unused column before you apply your filter. For example, let's say that the values on which you are going to filter are in column A, beginning in cell A3, and that your unused column is F. Then, in cell F3 you should enter the following formula:

=SUBTOTAL(103,$A$3:A3)

Copy this formula down as many cells as necessary. The formula should show a count for each item visible in column A. When you apply your filter, the formulas are updated to display a count for each filtered row.

As simple as this is, it does have a drawback: If you remove the filter, then the formula is updated to number (again) the visible rows. If, however, you had wanted the numbers to remain on the cells corresponding to the rows that had been previously filtered, then the SUBTOTAL approach won't work properly.

In that case, a different approach is required. In this example, let's assume that your unused column is G. And, after you apply the filter, your first visible row is 4. So, you would enter the following into cell G4:

=MAX($G$1:G3) + 1

Note that the range provided for the MAX function starts with the first cell in the column (make sure you include the dollar signs) and extends through the row above the cell in which you are entering the formula. Copy this formula down through all the remaining visible cells in the column. You now have a sequential count of all of the visible rows. This count remains even if you later remove the filter.

The only time this approach will be messed up is if you remove the filter, change the values in column A, and reapply or change the filter. This is because the approach presupposes that column G is empty before you add the formulas.

Perhaps the most robust approach is to use a macro to do the count. The following will add the count to column G based on which rows are visible at the time the macro is run:

Sub NumberVisibleRows()
    Dim J As Long
    Dim r As Range

    J = 0
    For Each r In Selection.Rows
        Cells(r.Row, 7) = ""
        If Not r.Hidden Then
            J = J + 1
            Cells(r.Row, 7) = J
        End If
    Next r
End Sub

To use the macro, apply your filter, select the rows you want evaluated, and then run it. You can modify which column is numbered by changing the 7 in Cells(r.Row, 7)—there are two instances—to reflect the column number you want.

If you would rather not have to select cells before running the macro, then you could use the following variation:

Sub NumberVisibleRows2()
    Dim J As Long
    Dim r As Range
    Dim iStart As Long

    iStart = 2

    J = 0
    For Each r In ActiveSheet.UsedRange.Rows
        Cells(r.Row, 7) = ""
        If Not r.Hidden And r.Row >= iStart Then
            J = J + 1
            Cells(r.Row, 7) = J
        End If
    Next r
End Sub

To make this work properly, just set the iStart variable equal to the row number you want the macro to pay attention to. Thus, in the above example, the first row will be ignored because iStart is equal to 2. This allows the macro to ignore any header rows you may have in your data.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8460) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.