Filtered Data - Row Numbers

[tallbarb]

As we all know, when you filter data (using AutoFilter), the row numbers associated with the filtered data don't change. What was in row 13 is still in row 13, even if, after filtering, it is VISUALLY in row 5 of the filtered data.

What I want to know - is there any way to make Excel adjust the row numbers to reflect the what appears to be its actual location in the filtered data?

The reason: I want to be able to see how many records I have left after filtering. Let's say I have a CITY field and I filter for SALEM. I may end up with 80 rows, each displaying their REAL row number as it appears unfiltered. It would be nice to be able to view the end of the data and see "81" as the row # (I know row 1 will always have to be the column headings) and, by subtracting 1 for the field names, I know I have 80 rows.

If I use the counta function, it still counts all the rows in that field, not just the filtered rows.

This is a no-brainer with a numeric field, as I can simply select the data in that column and use the status bar's autocalculate feature to see the count function. But this doesn't work with non-numeric data.

Maybe my brain is just tired - and I'm not seeing the obvious answer. Any suggestions you might have would be very appreciated!

 

[KatherineCoombs]

well it's not elegant, but you could select an entire column, go to Edit -->Go To-->Special-->Visible Cells Only and use the status bar to give you the count. Minus off 1 for the row header and that's your magic number.

Worked for me, but it might be too laborious?

Cheers,
Katherine

 

[weetnie]

Highlight Collumn, select count functions from bottom right tool menu.