Numbering rows

[nbslarson]

How can I number rows but omit any hidden rows from the numbering sequence?

 

[Mike H]

Hi,

This assumes your have date in column B that you will filter. Put this in A2
and fill down and it will number the rows and auto update if you change the
filter.

=SUBTOTAL(3,B$2:B2)

Mike

 

[nbslarson]

I may not be clear. I have information in rows 1,2,3,4. Row 5 is hidden.
Information in row 6,7,8,9,10. I want the numbering to exclude the hidden
row 5 so that the unhidden rows will be numbered: 1,2,3,4,5,6. . .

 

[Mike H]

Had you tried my suggestion you would have found that is exactly what it does.

 

[nbslarson]

Thank you, Mike. I did try your suggestion before I wrote the last post.
When I do that, the hidden rows are still included in the "numbering"
sequence of the un-hidden rows.

 

[Gord Dibben]

Try =SUBTOTAL(103,B$2:B2)

which accounts for manually hidden rows and rows hidden by filtering.

2003 and later versions.


Gord Dibben MS Excel MVP

 

[AL]

try using 9 instead of the 3 in the formula Mike gave you:
=SUBTOTAL(9,B$2:B2)