=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

In A2, =ROW-1 works until one filters rows. Also in A2,
=SUBTOTAL(3,$B$2:$B2) does work with filters but doesn't work with
hidden rows.

Is there code that will number rows consecutively even if one
_filters_ AND/OR manually _hides_ rows?? I've googled and googled the
archives but nothing comes up for this particular dual requirement.

Thanks. :oD

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

I have made several assumptions here, but try this formula:

=IF(ROW()=2,1,A1+1)

Is that what you need?

Thx
MSweetG222



"StargateFan" wrote:

In A2, =ROW-1 works until one filters rows. Also in A2,
=SUBTOTAL(3,$B$2:$B2) does work with filters but doesn't work with
hidden rows.

Is there code that will number rows consecutively even if one
_filters_ AND/OR manually _hides_ rows?? I've googled and googled the
archives but nothing comes up for this particular dual requirement.

Thanks. :oD

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

Oops -
That formula assumes you place it in A2.
For B2 change to: =IF(ROW()=2,1,B1+1)
(I have assumed you have header row in A1 and you don't want row 1 to be #'d
& you want row 2 to be numbered 1.)

--
Thx
MSweetG222



"MSweetG222" wrote:

I have made several assumptions here, but try this formula:

=IF(ROW()=2,1,A1+1)

Is that what you need?

Thx
MSweetG222



"StargateFan" wrote:

In A2, =ROW-1 works until one filters rows. Also in A2,
=SUBTOTAL(3,$B$2:$B2) does work with filters but doesn't work with
hidden rows.

Is there code that will number rows consecutively even if one
_filters_ AND/OR manually _hides_ rows?? I've googled and googled the
archives but nothing comes up for this particular dual requirement.

Thanks. :oD

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

On Wed, 22 Feb 2006 19:27:26 -0800, "MSweetG222"
wrote:

Oops -
That formula assumes you place it in A2.
For B2 change to: =IF(ROW()=2,1,B1+1)
(I have assumed you have header row in A1 and you don't want row 1 to be #'d
& you want row 2 to be numbered 1.)

--
Thx
MSweetG222

Hi, thanks!

I used this code and then tried hiding some rows but the row numbers
jump same as with the all the other codes and don't compensate for the
hidden rows. What am I doing wrong, pls?

I have XL2K on a new Windows 2000 installation and it's the first time
I've had W2K at home. Am finding that it's doing weird things to my
standard files that Win98SE never did, so wondering if there's a
problem with the Windows version number if this code works for you.

Pls advise and thanks. :oD

"MSweetG222" wrote:

I have made several assumptions here, but try this formula:

=IF(ROW()=2,1,A1+1)

Is that what you need?

Thx
MSweetG222



"StargateFan" wrote:

In A2, =ROW-1 works until one filters rows. Also in A2,
=SUBTOTAL(3,$B$2:$B2) does work with filters but doesn't work with
hidden rows.

Is there code that will number rows consecutively even if one
_filters_ AND/OR manually _hides_ rows?? I've googled and googled the
archives but nothing comes up for this particular dual requirement.

Thanks. :oD

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

Hello StargateFan,

Maybe I am not understanding your issue. This is a function formula (not
code).
You cut and pasted this formula into cell B2 and copied it down to all of
the cells in question? If yes, then you will have to post a small
sample/layout of your worksheet. Thx.

Thx
MSweetG222



"StargateFan" wrote:

On Wed, 22 Feb 2006 19:27:26 -0800, "MSweetG222"
wrote:

Oops -
That formula assumes you place it in A2.
For B2 change to: =IF(ROW()=2,1,B1+1)
(I have assumed you have header row in A1 and you don't want row 1 to be #'d
& you want row 2 to be numbered 1.)

--
Thx
MSweetG222

Hi, thanks!

I used this code and then tried hiding some rows but the row numbers
jump same as with the all the other codes and don't compensate for the
hidden rows. What am I doing wrong, pls?

I have XL2K on a new Windows 2000 installation and it's the first time
I've had W2K at home. Am finding that it's doing weird things to my
standard files that Win98SE never did, so wondering if there's a
problem with the Windows version number if this code works for you.

Pls advise and thanks. :oD

"MSweetG222" wrote:

I have made several assumptions here, but try this formula:

=IF(ROW()=2,1,A1+1)

Is that what you need?

Thx
MSweetG222



"StargateFan" wrote:

In A2, =ROW-1 works until one filters rows. Also in A2,
=SUBTOTAL(3,$B$2:$B2) does work with filters but doesn't work with
hidden rows.

Is there code that will number rows consecutively even if one
_filters_ AND/OR manually _hides_ rows?? I've googled and googled the
archives but nothing comes up for this particular dual requirement.

Thanks. :oD

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

On Thu, 23 Feb 2006 09:24:26 -0800, "MSweetG222"
wrote:

Hello StargateFan,

Maybe I am not understanding your issue. This is a function formula (not
code).

Yes, as per normal.

You cut and pasted this formula into cell B2 and copied it down to all of
the cells in question? If yes, then you will have to post a small
sample/layout of your worksheet. Thx.

Yes, did so, just as I might have done with example code I gave of
what didn't work <g. I put in A2 in this case as that is column in
question and I do have a header row, as you correctly assumed <vbg:

Here is the small sample of date/layout copied directly from the
sheet:

# Date Year
01 2005.07.15.Fri 1
02 2005.07.22.Fri 1
03 2005.07.29.Fri 1
04 2005.08.05.Fri 1
05 2005.08.12.Fri 1
06 2005.08.19.Fri 1
07 2005.08.26.Fri 2
08 2005.09.09.Fri 2
09 2005.09.16.Fri 2
10 2005.09.16.Fri 2

In the column marked "#", I have in A2 the following formula, as per
what you kindly provided: =IF(ROW()=2,1,A1+1).

Here's the problem, when I manually hide rows 4 to 6, for example,
this is what happens to the numbering which defeats the purpose of
having it:

01 2005.07.15.Fri
02 2005.07.22.Fri
03 2005.07.29.Fri
07 2005.08.26.Fri
08 2005.09.09.Fri
09 2005.09.16.Fri
10 2005.09.16.Fri

Thank you! Appreciate any help in fixing this and hope there is a
solution. Up till now have only filtered rows and formulas have
worked for that, but not for hiding rows.

Cheers! :oD

Thx
MSweetG222



"StargateFan" wrote:

On Wed, 22 Feb 2006 19:27:26 -0800, "MSweetG222"
wrote:

Oops -
That formula assumes you place it in A2.
For B2 change to: =IF(ROW()=2,1,B1+1)
(I have assumed you have header row in A1 and you don't want row 1 to be #'d
& you want row 2 to be numbered 1.)

--
Thx
MSweetG222

Hi, thanks!

I used this code and then tried hiding some rows but the row numbers
jump same as with the all the other codes and don't compensate for the
hidden rows. What am I doing wrong, pls?

I have XL2K on a new Windows 2000 installation and it's the first time
I've had W2K at home. Am finding that it's doing weird things to my
standard files that Win98SE never did, so wondering if there's a
problem with the Windows version number if this code works for you.

Pls advise and thanks. :oD

"MSweetG222" wrote:

I have made several assumptions here, but try this formula:

=IF(ROW()=2,1,A1+1)

Is that what you need?

Thx
MSweetG222



"StargateFan" wrote:

In A2, =ROW-1 works until one filters rows. Also in A2,
=SUBTOTAL(3,$B$2:$B2) does work with filters but doesn't work with
hidden rows.

Is there code that will number rows consecutively even if one
_filters_ AND/OR manually _hides_ rows?? I've googled and googled the
archives but nothing comes up for this particular dual requirement.

Thanks. :oD

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

StarGateFan,

I found a posting on your question. I have cut our conversion and posted
their conversation below.

Here is a modifed version of their UDF to Count Visible Rows:


Function CountVisible(rng As Range)
Dim cell As Range
For Each cell In rng
If Not cell.EntireRow.Hidden Then
CountVisible = CountVisible + 1
End If
Next cell
End Function



The older versions of Excel apparently do not recognize manually hidding
rows as an event that triggers a worksheet calc. I had to press F9 and the
formulas updated to count the rows of the visible rows. Hope that is okay
for your purposes.

Do you know how to incorporate a UDF into your worksheet?
1. Open Worksheet in question
2. Alt F11
3. Insert (on Main Menu Bar)
4. Module (from Drop Down List - Note: NOT Class Module)
5. Cut and Paste above UDF into the module
6. Alt Q (close & return to Excel)
7. Use the UDF like your would any other function formula.
8. Don't forget to save Worksheet

Paste the following into Cell A2
=CountVisible($A$1:A1)

Good Luck!
P.S. I like StarGate also.

Thx
MSweetG222

================================================== =

Subject: Sum only visible cells 12/16/2005 8:36 AM PST

By: Bob Phillips In: microsoft.public.excel.programming


If your rows are manually hidden and you wan t to sum the visible rows
pre-2003, you can use this UDF

Function SumVisible(rng As Range)
Dim cell As Range
For Each cell In rng
If Not cell.EntireRow.Hidden Then
SumVisible = SumVisible + cell.Value
End If
Next cell
End Function


=SumVisible(A1:A10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charlie" wrote in message
...
Thanks, guys. Now all I need to do is get my employer to upgrade to 2003!

"Ron Coderre" wrote:

NICE! Thanks, Tom. I did not know that.
I briefly had XL2003 on my PC, but it wouldn't interface with a legacy
application I need to use. So, alas, I had to back down to XL2002.


***********
Regards,
Ron


"Tom Ogilvy" wrote:

In Excel 2003, subtotal has options to ignore any hidden row - not
just
those rows hidden by a filter.

--
Regards,
Tom Ogilvy

"Ron Coderre" wrote in message
...
If you hide the rows with an advanced filter or an autofilter, you
can use
the sum version of the SUBTOTAL function to display totals for
visible
items.

Example:

Col_A has Name
Col_B has Amount

A2: Name
A3: Bill
A4: Dave
etc

B2: 10
B3: 20
etc

B1: =SUBTOTAL(9,B2:B10)
intially returns 30.
(The 9 in the SUBTOTAL function tells Excel to ADD the amounts,
other
options are AVERAGE, MIN, MAX...etc....check Excel Help)

If you autofilter to only show Bill, the formula wil return 10.

Does that help?

***********
Regards,
Ron


"Charlie" wrote:

Is there a formula to sum only visible cells, so that if I hide a
row
the
formula result will change?

(In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)

Or will I need to write a function to do this?

TIA
Charlie

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

If you are using Excel 2003 you could use:
"=SUBTOTAL(103,$B$2:$B2)"

This won't include data in hidden rows or any rows that are not
included in the result of a filter.

HTH--Lonnie M.

=ROW-1, =SUBTOTAL(3,$B$2:$B2) don't work in hidden rows.
 

On Fri, 24 Feb 2006 07:37:30 -0800, "MSweetG222"
wrote:

StarGateFan,

I found a posting on your question. I have cut our conversion and posted
their conversation below.

Here is a modifed version of their UDF to Count Visible Rows:


Function CountVisible(rng As Range)
Dim cell As Range
For Each cell In rng
If Not cell.EntireRow.Hidden Then
CountVisible = CountVisible + 1
End If
Next cell
End Function



The older versions of Excel apparently do not recognize manually hidding
rows as an event that triggers a worksheet calc. I had to press F9 and the
formulas updated to count the rows of the visible rows. Hope that is okay
for your purposes.

Do you know how to incorporate a UDF into your worksheet?
1. Open Worksheet in question
2. Alt F11
3. Insert (on Main Menu Bar)
4. Module (from Drop Down List - Note: NOT Class Module)
5. Cut and Paste above UDF into the module
6. Alt Q (close & return to Excel)
7. Use the UDF like your would any other function formula.
8. Don't forget to save Worksheet

Paste the following into Cell A2
=CountVisible($A$1:A1)

Thank you for such detailed instructions, this is great! I'll try
this out now.

Good Luck!
P.S. I like StarGate also.

<vbg Excellent! I'm a big fan of both series and the movie! :oD

Thx
MSweetG222

================================================= ==

Subject: Sum only visible cells 12/16/2005 8:36 AM PST

By: Bob Phillips In: microsoft.public.excel.programming


If your rows are manually hidden and you wan t to sum the visible rows
pre-2003, you can use this UDF

Function SumVisible(rng As Range)
Dim cell As Range
For Each cell In rng
If Not cell.EntireRow.Hidden Then
SumVisible = SumVisible + cell.Value
End If
Next cell
End Function


=SumVisible(A1:A10)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Charlie" wrote in message
...
Thanks, guys. Now all I need to do is get my employer to upgrade to 2003!

"Ron Coderre" wrote:

NICE! Thanks, Tom. I did not know that.
I briefly had XL2003 on my PC, but it wouldn't interface with a legacy
application I need to use. So, alas, I had to back down to XL2002.


***********
Regards,
Ron


"Tom Ogilvy" wrote:

In Excel 2003, subtotal has options to ignore any hidden row - not
just
those rows hidden by a filter.

--
Regards,
Tom Ogilvy

"Ron Coderre" wrote in message
...
If you hide the rows with an advanced filter or an autofilter, you
can use
the sum version of the SUBTOTAL function to display totals for
visible
items.

Example:

Col_A has Name
Col_B has Amount

A2: Name
A3: Bill
A4: Dave
etc

B2: 10
B3: 20
etc

B1: =SUBTOTAL(9,B2:B10)
intially returns 30.
(The 9 in the SUBTOTAL function tells Excel to ADD the amounts,
other
options are AVERAGE, MIN, MAX...etc....check Excel Help)

If you autofilter to only show Bill, the formula wil return 10.

Does that help?

***********
Regards,
Ron


"Charlie" wrote:

Is there a formula to sum only visible cells, so that if I hide a
row
the
formula result will change?

(In B6) =SUMIF(A1:A5,NOT( [cell row hidden] ),B1:B5)

Or will I need to write a function to do this?

TIA
Charlie