J
Jason Kendall
My user has a spreadsheet with some columns of dollar values.
What he wants to do is to set up an Auto Filter on the data with a Sum
cell at the bottom of the column (or even elsewhere on the page) such
that the sum will adjust to show only the sum of those values that are
visible in the originally selected range, which includes all of the
cells in that column, for the data set.
I'm not an Excel programmer so I don't know the VBA object model. I
have read all of the newsgroup posts back to 05/15/05 that have the
word 'sum' in the subject and I've seen a lot of things that look like
they might be the answer, but I can't seem to crack this nut.
I know this CAN'T be hard and I actually expect that there's already
an Excel function to do this, but I just can't find it.
My very closest attempt is this one line function shown below, which
doesn't seem to work.
Public Function SumVisibleRows(ByVal TheRange As Range) As Currency
SumVisibleRows =
CCur(Application.Sum(TheRange.SpecialCells(xlCellTypeVisible)))
End Function
Thanks for any help!
-Jason Kendall
(e-mail address removed)
What he wants to do is to set up an Auto Filter on the data with a Sum
cell at the bottom of the column (or even elsewhere on the page) such
that the sum will adjust to show only the sum of those values that are
visible in the originally selected range, which includes all of the
cells in that column, for the data set.
I'm not an Excel programmer so I don't know the VBA object model. I
have read all of the newsgroup posts back to 05/15/05 that have the
word 'sum' in the subject and I've seen a lot of things that look like
they might be the answer, but I can't seem to crack this nut.
I know this CAN'T be hard and I actually expect that there's already
an Excel function to do this, but I just can't find it.
My very closest attempt is this one line function shown below, which
doesn't seem to work.
Public Function SumVisibleRows(ByVal TheRange As Range) As Currency
SumVisibleRows =
CCur(Application.Sum(TheRange.SpecialCells(xlCellTypeVisible)))
End Function
Thanks for any help!
-Jason Kendall
(e-mail address removed)