Excel 2000 to Excell 97 Vba Problem
 

I was wondering if anyone had any ideas on how to Sum only visible cell
in a particular range. I then take this info and put it into a text bo
in a userform.

I'm currently using the function suggested on the microsoft website
but that only seems to work for Excel 2000, and not Excel 97.

Either a function or sub would be great, but they must work in Exce
97.

This thing is killing me here.

Thanks in Advance

Jame

--
Message posted from http://www.ExcelForum.com

Excel 2000 to Excell 97 Vba Problem
 

James,


use application.worksheetfunction.subtotal(9, range).

Subtotal has many subfucntions (as the 9 parameter) suggests, but all
the functions only operate on visible rows.


---
Message posted from http://www.ExcelForum.com/

Excel 2000 to Excell 97 Vba Problem
 

That is true only if the rows are visible or hidden as a result of a filter.
If that is the case, the Worksheetfunction.Subtotal(9,Range) would get the
sum of the visible cells. If they have been hidden using code or manually,
then subtotal would not work.

in either case
Dim rng as Range, rng1 as Range
set rng = Range("B9:B35")
set rng1 = rng.Specialcells(xlVisible)
msgbox worksheetFunction.Sum(rng1)

would sum just the visible cells.

--
Regards,
Tom Ogilvy



Kieran wrote in message
...
James,


use application.worksheetfunction.subtotal(9, range).

Subtotal has many subfucntions (as the 9 parameter) suggests, but all
the functions only operate on visible rows.


---
Message posted from http://www.ExcelForum.com/

Excel 2000 to Excell 97 Vba Problem
 

you guys are too good.

thanks so much for the help. You may have just saved me a monitor an
a trip to the emergency room.

Thanks again

--
Message posted from http://www.ExcelForum.com