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

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

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



"Barb Reinhardt" wrote:

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

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

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

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


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

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

Try:

=VisTotal((A1,A4,A10))


"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

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

--


Gary


"JMB" wrote in message
...
Try:

=VisTotal((A1,A4,A10))


"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

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

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



Gary Keramidas wrote:

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

--

Gary

"JMB" wrote in message
...
Try:

=VisTotal((A1,A4,A10))


"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

--

Dave Peterson

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 . . .



--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

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

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



Gary Keramidas wrote:

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

--

Gary

"JMB" wrote in message
...
Try:

=VisTotal((A1,A4,A10))


"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

--

Dave Peterson

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

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

Tom Ogilvy wrote:

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 . . .

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote:

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

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



Gary Keramidas wrote:

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

--

Gary

"JMB" wrote in message
...
Try:

=VisTotal((A1,A4,A10))


"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

--

Dave Peterson


--

Dave Peterson

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

--


Gary


"Dave Peterson" wrote in message
...
Try some test data in A1:E1 and hide columns C and D.

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



Gary Keramidas wrote:

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

--

Gary

"JMB" wrote in message
...
Try:

=VisTotal((A1,A4,A10))


"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

--

Dave Peterson

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






"Gary Keramidas" wrote:

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

--


Gary


"JMB" wrote in message
...
Try:

=VisTotal((A1,A4,A10))


"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