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

"Rick Rothstein (MVP - VB)" wrote:

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


"Barb Reinhardt" wrote in message
...
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



"James T" wrote:

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