On Sun, 06 Jun 2004 12:04:29 -0400, StargateFan
wrote:

On Sat, 5 Jun 2004 21:14:55 -0400, "Bernie Deitrick" <deitbe @
consumer dot org wrote:

StargateFan,

Let's say that you have labels in row 1, and want your row numbering in
column A.

In Cell A2, use the formula

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

and copy down to match your data. Then, when you filter, the row numbering
will update as you desire.

Kewl, will give this a try right now!

Well, I took the coding to the office. It seemed to work just great.
I tested it out and whenever I did a filter, the rows that showed up
numbered true; as well as when all filters were off. That seemed
great.

But then I kept adding rows at the bottom after the few blank ones
that were there so I could see how it acted. Past row #60, something
very weird took place. All rows after that were labelled 61! Though
the coding that worked before didn't change, whether the rest of the
row had data or not other than the row label, and even though Excel
modified each row coding correctly to reflect new cell address, just
as it did in the numbers early, I got all #61s!!

So I came home to try it out on a brand new sheet and set up a sheet
here quickly; yet something ever weirder happened here!! ALL rows
here at home got named "0"! Is that weird, or what?

Anyone know what in heaven's name is going on? <g (Before I think I
have to be taken away to a funny farm <lol!)

Thanks!

Thanks!

HTH,
Bernie
MS Excel MVP

"StargateFan" wrote in message
. ..
The row coding works wonderfully. I've used it for several years now.
No matter what rows are deleted in a worksheet, the rows numbered with
this coding adapt and you don't have any numbering get out of order.

But I ran into a problem with one spreadsheet coded with this that I
had to create yesterday; the numbering jumps over the hidden rows that
aren't shown when the spreadsheet was filtered..

What I mean is that if I choose to filter the worksheet by a certain
criteria, the rows that don't follow this criteria are hidden, but the
rows that show up don't adapt to show the numbering true to the number
of rows now appearing. This is usu. a good thing, but not in this
particular speadsheet where the row numbering was to reflect how many
records fell under the criteria - either not filtered or filtered by
various criteria.

So what does this mean?

Let's say rows 1, 3, 7, and 10 show up in a filter out of rows 1, 2,
3, 4 ... 10. The rows that appear then are numbered 1, 3, 7 and 10
and not 1, 2, 3 and 4, which would be what is needed here. So row 3
would then become 2, 7 would become 3, and 10 would become 4. Then
when unfiltered, they'd go back to the numbering they fall under.

Again, most of the time this is good, but not in this one sheet.

Is there coding for row numbering that takes into account filtering
and adapts accordingly? It's a long shot, I know, but it would be
good. I don't know what I'll do if there isn't something like this.

p.s., I'm a real VB newbie, just to let everyone know ahead of time.
<g

Thanks!