Did I stump everyone? Re: Counter that ignores hidden rows

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!

this code fragement will sum the values in rows 1 to 10 for any row that is not hidden

Please Login or Register  to view this content.

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" <TechMGR@discussions.microsoft.com> wrote in message
news:A14C05DC-DBC8-4FEE-B71F-6EE12B6E99BD@microsoft.com...
> 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!

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" <TechMGR@discussions.microsoft.com> wrote in message
> news:A14C05DC-DBC8-4FEE-B71F-6EE12B6E99BD@microsoft.com...
> > 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!
>
>
>