That worked like a charm Bernie! I wasn't really understanding how the
function worked but now it all makes sense!
Thank you very much!

"Bernie Deitrick" wrote:

TechMGR,

No, you didn't stump everyone - you replied to your own message, which makes
most folks (including myself) think that the question was answered. Why
bother reading an answered question?

Anyway, the way you do this is to simply use this in cell B4477:

=SUBTOTAL(3,$C$4477:C4477)

(or any other column in your data set), where 4477 is the first row of data.
Then copy it down to match your data set, and when it is filtered, the
SUBTOTAL function will update properly.

If you have the latest version SUBTOTAL will also work with hidden rows, not
just filtered rows.

HTH,
Bernie
MS Excel MVP


"TechMGR" wrote in message
...
As I never got any response to my previous question "How to create a
counter
that ignores hidden rows?", I'm assuming that either I stumped everyone or
I
did a lousy job of posing the question. Let me try again...

I am trying to create a simple counter that does not index if the row that
it is on is hidden. I have tried using the SUBTOTAL function as
follows...

I set Cells B4477=1 and B4478=2

The formula for Cell B4479 is then written as...

=SUBTOTAL(103,$B$4477:B4478)+1

The formula for Cell B4480 is then ...

=SUBTOTAL(103,$B$4477:B4479)+1 and so on for the consequent cells down
the column.

It works for the 1st occurence (i.e. Cell B4479=3) but the rest of the
cells
down the column are also equal to three, as if the subtotal function is
not
recognizing the VALUE results of previous functions? I have tried both
the
"COUNTA" version and the "COUNT" version with no success. :(

I also tried the following macro but for the life of me I can't figure out
why it doesn't work either...

Sub AUTOFILL_NOTE_NUMBERS()
'
Dim RowNum, ColNum
RowNum = 4477 ' Initialize variables.
ColNum = "B"
Do While RowNum < 4600
RowNum = RowNum + 1 ' Increment Counter.
If Rows("RowNum:RowNum").EntireRow.Hidden = False Then
Range("ColNum & RowNum") = Range("ColNum & RowNum") + 1
Else
Range("ColNum & RowNum") = 0
End If
Loop
End Sub

Thanks!