Sum Non Grouped Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Winshent - 19 Oct 2004 14:55 GMT

I have a spread which stores past and present data.

I want to be able to show a sum of the data that is visible before and
after grouping is applied. I have some code which runs when an
autofilter is used (see below), but this doesnt work for grouping.

Public Function SumVisible(rng)

Dim CellSum As Long
Dim Cell As Range
Application.Volatile

CellSum = 0
Set rng = Intersect(rng.Parent.UsedRange, rng)

For Each Cell In rng
If IsNumeric(Cell) Then
If Not Cell.EntireRow.Hidden And Not Cell.EntireColumn.Hidden Then
CellSum = CellSum + Cell
End If
End If
Next Cell

SumVisible = CellSum

End Function

Reply to this Message

Don Guillett - 19 Oct 2004 15:39 GMT

have a look at specialcells.
SpecialCells Method
See Also Applies To Example Specifics
Returns a Range object that represents all the cells that match the
specified type and value. Range object.

expression.SpecialCells(Type, Value)

expression Required. An expression that returns one of the objects in the
Applies To list.

Type Required XlCellType. The cells to include.

XlCellType can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

Value Optional Variant. If Type is either xlCellTypeConstants or
xlCellTypeFormulas, this argument is used to determine which types of cells
to include in the result. These values can be added together to return more
than one type. The default is to select all constants or formulas, no matter
what the type. Can be one of the following XlSpecialCellsValue constants:

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

Example
This example selects the last cell in the used range of Sheet1.

Worksheets("Sheet1").Activate
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell-- Don GuillettSalesAid

> I have a spread which stores past and present data.
>
[quoted text clipped - 22 lines]
>
> End Function

Reply to this Message