Range properties from Array variables

[Matthew Pfluger]

I know that it is faster to grab an range of cells and send it to an array.
Can I then access Range properties from that array? For example, I want to
Sum all cells in a range that are not header rows (i.e. rows that contain a
'+'). To do this, I need to check the OutlineLevel property.

Example code:

Public Function SumIfNotHeading(ByRef rng As Range) As Double

SumIfNotHeading = 0

' Add cell value if row has not a header
Dim cell As Range
For Each cell In rng
If Not bIsHeading(cell) Then
SumIfNotHeading = SumIfNotHeading + cell.Value
End If
Next cell

End Function

Function bIsHeading(ByRef rng As Range) As Boolean
bIsHeading = (rng.EntireRow.OutlineLevel < rng.Offset(1,
0).EntireRow.OutlineLevel)
End Function

Is this possible, or is there a better way? Thanks.

Matthew Pfluger

 

[ilia]

When you read a range into an array, you only get its values, not any
of the other cell properties.

Here's an idea. This isn't the safest technique in the world, but you
could add everything, and then divide it by maximum outline level. In
other words, if all subtotals are sums, and your outline level is
four, then adding up all the subtotals will be four times the actual
sum. Here's my sample data (some random numbers):

Category 1 Group 1 Item 1 56
Category 1 Group 1 Item 2 37
Category 1 Group 1 Item 3 99
Category 1 Group 1 Item 4 62
Category 1 Group 1 Item 4 7
Category 1 Group 1 total 261
Category 1 Group 2 Item 2 5
Category 1 Group 2 Item 2 62
Category 1 Group 2 Item 3 20
Category 1 Group 2 Item 3 82
Category 1 Group 2 Item 3 65
Category 1 Group 2 total 234
Category 1 Group 3 Item 1 65
Category 1 Group 3 Item 1 72
Category 1 Group 3 Item 2 26
Category 1 Group 3 Item 3 78
Category 1 Group 3 Item 4 55
Category 1 Group 3 total 296
Category 1 total 791
Category 2 Group 1 Item 1 61
Category 2 Group 1 Item 1 60
Category 2 Group 1 Item 2 62
Category 2 Group 1 Item 3 96
Category 2 Group 1 Item 4 42
Category 2 Group 1 total 321
Category 2 Group 2 Item 1 40
Category 2 Group 2 Item 1 93
Category 2 Group 2 Item 4 25
Category 2 Group 2 Item 4 57
Category 2 Group 2 total 215
Category 2 Group 3 Item 2 76
Category 2 Group 3 Item 2 45
Category 2 Group 3 Item 3 6
Category 2 Group 3 Item 3 95
Category 2 Group 3 Item 4 87
Category 2 Group 3 total 309
Category 2 total 845
GRAND TOTAL 1636

All of the numbers in the fourth column:
56+37+99+62+7+261+5+62+20+82+65+234+65+72+26+78+55+296+791+61+60+62+96+42+321+40+93+25+57+215+76+45+6+95+87+309+845+1636
Evaluates to 6544

All of the numbers in the fourth column except the grand total:
56+37+99+62+7+261+5+62+20+82+65+234+65+72+26+78+55+296+791+61+60+62+96+42+321+40+93+25+57+215+76+45+6+95+87+309+845
Evaluates to 4908

All of the numbers in the fourth column except the grand total and
category totals:
56+37+99+62+7+261+5+62+20+82+65+234+65+72+26+78+55+296+61+60+62+96+42+321+40+93+25+57+215+76+45+6+95+87+309
Evaluates to 3272

All of the numbers in the fourth column without any of the subtotals:
56+37+99+62+7+5+62+20+82+65+65+72+26+78+55+61+60+62+96+42+40+93+25+57+76+45+6+95+87
Evaluates to 1636

Max outline level 4: 6544\4=1636
Max outline level 3: 4908\3=1636
Max outline level 2: 3272\2=1636

This will also work for grouped totals. So if you know your outline
level in advance or can figure it out at runtime without looping
through the entire range, and have enough data to warrant the
efficiency consideration, you can just get the entire sum and perform
the division as outlined.

Hope that helps.

 

[Matthew Pfluger]

That's a good suggestion, one that I hadn't thought of. I wonder how it
would work if the sub categories did not all contain the same amount of
levels (ie Category 1 Group 1 - no sub items).

Thanks for answering the Range to array question, too.

Matthew Pfluger