Sum cells, excluding hidden columns

[Guest]

Hi all

I have a nice little spreadsheet (well not so little) and a requirement in a
column to sum preceeding columns of data. However I don't want to include
hidden columns.

I found this nice little vba programming on a site somewhere (have to love
google). And it works perfectly if summing cells that are in a range i.e.
A1:A10.

Function VisTotal(Rg As Range)
Dim x, tot
Application.Volatile
tot = 0
For Each x In Rg
If x.ColumnWidth = 0 Or x.RowHeight = 0 Then Else tot = tot + x
Next
VisTotal = tot
End Function

However it does not work if the range is not consistent i.e. A1,A4,A10. It
will work if I refer to named ranges, however I would need to create over 200
named ranges for this (bit of a job).

Any ideas on how this VBA could be modified so it will deal with a non
consistent range like A1,A4,A10??

Regards

James

 

[Guest]

This isn't as "elegant" as I'd like, but it works for 1 to 5 ranges.


Function VisTotal(Rng1 As Range, Optional Rng2 As Range, Optional Rng3 As
Range, Optional Rng4 As Range, Optional Rng5 As Range)
Dim myRange As Range
Debug.Print Rng1.Address, Rng2.Address

Dim x, tot

If Not Rng2 Is Nothing Then
Set myRange = Union(Rng1, Rng2)
End If
If Not Rng3 Is Nothing Then
Set myRange = Union(myRange, Rng3)
End If
If Not Rng4 Is Nothing Then
Set myRange = Union(myRange, Rng4)
End If
If Not Rng5 Is Nothing Then
Set myRange = Union(myRange, Rng5)
End If

Application.Volatile
tot = 0
For Each x In myRange
If x.ColumnWidth = 0 Or x.RowHeight = 0 Then Else tot = tot + x
Next
VisTotal = tot
End Function

 

[Guest]

Hi Barb,

Thank you for the prompt response

Just tried your vba (must admit I just did a copy and paste).

I then created formula =Vistotal(A2,A10)

I get an error "Compile error: expected type name", and it highlights
"Function VisTotal(Rng1 As Range, Optional Rng2 As Range, Optional Rng3 As
Range, Optional Rng4 As Range, Optional Rng5 As Range)" in red.

Any ideas?

Regards

James

 

[Rick Rothstein \(MVP - VB\)]

How about this modification to your function which should work for virtually
as many ranges as you want to include...

Function VisTotal(ParamArray Ranges()) As Variant
Dim R As Variant
Dim myRange As Range
Set myRange = Ranges(0)
For Each R In Ranges
If Not R Is Ranges(0) Then Set myRange = Union(myRange, R)
Next
Application.Volatile
For Each R In myRange
If R.ColumnWidth > 0 And R.RowHeight > 0 Then _
VisTotal = VisTotal + R.Value
Next
End Function

Rick

 

[Guest]

I knew there was a better way to do it. Thanks.

How about this modification to your function which should work for virtually
as many ranges as you want to include...

Function VisTotal(ParamArray Ranges()) As Variant
Dim R As Variant
Dim myRange As Range
Set myRange = Ranges(0)
For Each R In Ranges
If Not R Is Ranges(0) Then Set myRange = Union(myRange, R)
Next
Application.Volatile
For Each R In myRange
If R.ColumnWidth > 0 And R.RowHeight > 0 Then _
VisTotal = VisTotal + R.Value
Next
End Function

Rick

 

[Guest]

Try:

=VisTotal((A1,A4,A10))

 

[Gary Keramidas]

if using 2003 or greater, why not just use the built-in subtotal function?
just wondering.

 

[Rick Rothstein \(MVP - VB\)]

Actually, as it turns out, we can simplify this function a lot more...

Function VisTotal(ParamArray Ranges()) As Variant
Dim R As Variant
Dim C As Variant
Application.Volatile
For Each R In Ranges
For Each C In R
If C.ColumnWidth > 0 And C.RowHeight > 0 Then _
VisTotal = VisTotal + C.Value
Next
Next
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

Actually, as it turns out, we can simplify this function a lot more...

Function VisTotal(ParamArray Ranges()) As Variant
Dim R As Variant
Dim C As Variant
Application.Volatile
For Each R In Ranges
For Each C In R
If C.ColumnWidth > 0 And C.RowHeight > 0 Then _
VisTotal = VisTotal + C.Value
Next
Next
End Function

And, while some might not consider this "simpler", we can simplify the
function just a wee bit more...

Function VisTotal(ParamArray Ranges()) As Variant
Dim R As Variant
Dim C As Variant
Application.Volatile
For Each R In Ranges
For Each C In R
VisTotal = VisTotal - C.Value * (C.ColumnWidth > 0 And C.RowHeight >
0)
Next
Next
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

And, while some might not consider this "simpler", we can simplify the
function just a wee bit more...

Function VisTotal(ParamArray Ranges()) As Variant
Dim R As Variant
Dim C As Variant
Application.Volatile
For Each R In Ranges
For Each C In R
VisTotal = VisTotal - C.Value * (C.ColumnWidth > 0 And C.RowHeight >
0)

Damn! The above line wrapped. It should have been this...

VisTotal = VisTotal - C.Value * (C.ColumnWidth > 0 And C.RowHeight > 0)

Rick

 

[Dave Peterson]

Try some test data in A1:E1 and hide columns C and D.

and put this in F1: =SUBTOTAL(103,A1:E1)

 

[Guest]

Why not just quote the help:

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges . . .

 

[Gary Keramidas]

i see, it says it's not designed for rows.
thanks

 

[Dave Peterson]

I think I learn more by trial (and lots of errors).

And sometimes, the help isn't right <bg>.

 

[Guest]

I guess Gary saw it as "RTFM". <g>

 

[Dave Peterson]

Gary is too industrious. <hehe>

I guess Gary saw it as "RTFM". <g>

 

[Gary Keramidas]

i'm too something, but i don't know if it's industrious<g>

 

[Guest]

I only have XL 2000. I'm aware of the extra Subtotal options in XL03, but
don't have any firsthand experience using them. I pondered your question
myself, but couldn't test it so didn't say anything. In short, I didn't
recommend it due to ignorance, not due to any special knowledge <g>