SUM VISIBLE COLUMNS ONLY

[jammer6_9]

In summing rows, =subtotal(109,rng) works... In summing columns it's not? :banghead:

[tstav]

=SUBTOTAL(109,A2:E2) gives the SUM of A2:E2 just fine.

Hiding columns though, will have no effect (i.e. result will not change), contrary to SUBTOTALing rows, where hiding rows will alter the result.

[Simon Lloyd]

How about something along the lines:

Application.WorksheetFunction.Subtotal(109, Rng.SpecialCells(xlCellTypeVisible))

[jammer6_9]

=SUBTOTAL(109,C8:N8) <--- I just dont know why this does not work. Is this maybe because of I am hiding columns by this code?


If Range("L3").Value <> 1 Then
Range("L:L").EntireColumn.Hidden = True
Else
If Range("L3").Value = 1 Then
Range("L:L").EntireColumn.Hidden = False
End If
End If



=SUBTOTAL(109,A2:E2) gives the SUM of A2:E2 just fine.

Hiding columns though, will have no effect (i.e. result will not change), contrary to SUBTOTALing rows, where hiding rows will alter the result.

[jammer6_9]

This works! :whistle:


Range("o8").Value = Application.WorksheetFunction.Subtotal(109, Range("c8:n8"). _
SpecialCells(xlCellTypeVisible))




How about something along the lines:

Application.WorksheetFunction.Subtotal(109, Rng. _
SpecialCells(xlCellTypeVisible))

[tstav]

To get this thread back on the air, I would like to mention something that puzzles me (I will use the example of the posts that came before).

To sum the visible cells' values in a row, we used
Range("A8").Value = Application.WorksheetFunction.Subtotal(109, Range("C8:F8") _
.SpecialCells(xlCellTypeVisible))
which works fine.

I put this statement in a function, in order to use it as a formula for cell A8.
Function SumVisibleCellsInRow(ByVal rng As Range) As Single
SumVisibleCellsInRow = Application.WorksheetFunction.SubTotal(109, rng. _
SpecialCells(xlCellTypeVisible))
End Function

and then A8=SumVisibleCellsInRow(C8:F8)

To my surprise it turns out that the function ignores any hiding of columns (C,D,E,or F) and keeps returning the sum of all cells (C8 through F8).

Can anyone replicate this example and post back?

[tstav]

Can anyone replicate the example of the previous post and see what happens?

[Aussiebear]

I'm a bit lost on this but....


Range("o8").Value = Application.WorksheetFunction.Subtotal(109, Range("c8:n8"). _
SpecialCells(xlCellTypeVisible))
is different from your version

Range("A8").Value = Application.WorksheetFunction.Subtotal(109, Range("C8:F8"). _
SpecialCells(xlCellTypeVisible))
in so far as the initial one has a "O8" where yours has "A8" as its reference point. Could this be why?

[tstav]

Hi Ted,
thanks for answering.
The straight assignment of the worksheetfunction to a cell's value, works fine for both the O8 and the A8. It's not a matter of which cell one assigns this worksheetfunction to.

What puzzles me is: Why doesn't it work when the assignment is made through a formula that calls the function I'm using in post#6?

Practically, both ways (see post#6) do exactly the same thing. Yet it doesn't work when the function is used.

Can anyone see something that I seem to overlook without realizing it?

[Aussiebear]

OKay.. what about the difference then in the specified range"

One says


Range("A8").Value = Application.WorksheetFunction.Subtotal(109, Range("C8:F8") _
.SpecialCells(xlCellTypeVisible))
by naming the range C8:F8 and the other just says


Function SumVisibleCellsInRow(ByVal rng As Range) As Single
SumVisibleCellsInRow = Application.WorksheetFunction.SubTotal(109, rng. _
SpecialCells(xlCellTypeVisible))
End Function

as in rng.SpecialCells etc.

[tstav]

I put this statement in a function, in order to use it as a formula for cell A8.

Function SumVisibleCellsInRow(ByVal rng As Range) As Single
SumVisibleCellsInRow = Application.WorksheetFunction.SubTotal(109, rng.SpecialCells(xlCellTypeVisible))
End Function

and then A8=SumVisibleCellsInRow(C8:F8)



I use the function in the formula in cell A8, therefore the range C8:F8 is sent to the function.


But anyway, it doesn't have to be this specific range (I only use it as an example).


Try using ANY range of your own choice.
First, use the formula in a cell in any sheet with all columns in your range being visible.
Then, hide ANY column of this range.
Is the result of the formula different? (It should be, but is it??).

It just so happens in all my tests, that the result does not change.
It doesn't make any sense at all, and that is why I'm wondering whether I'm not seeing something that might be obvious to you (or anybody else...........)

[mdmackillop]

I get the same result, also using a simplified Sum. The cell count includes the hidden column


Function test(data As Range)
Dim rng As Range
Set rng = data.SpecialCells(xlCellTypeVisible)
MsgBox rng.Cells.Count
test = Application.WorksheetFunction.Sum(rng)
End Function

[tstav]

Thanks for looking into it Malcolm,

I'll keep testing on this. There should be a clue somewhere as to why this is so...

[mikerickson]

I think that SpecialCells in a UDF reacts differently when called from a spreadhseet formula than from VB.
A button that called VisTest gave me different results from the cell containing =VisCount(A1:F10).
The button responded according to the rows or columns being hidden.
The cell showed 60, no matter what was hidden.
Sub VisTest()
MsgBox visCount(Range("a1:f10"))
End Sub

Function visCount(inRange As Range) As Double
Application.Volatile
visCount = inRange.SpecialCells(xlCellTypeVisible).Count
End Function

[tstav]

SpecialCells in a UDF reacts differently when called from a spreadhseet formula

Yes Mike,
I was so surprised a few days ago when I came across this issue in a test I was doing. That's why I brought it up here.

Reacts differently? I would say it doesn't react at all.
It just stood there, doing nothing, just looking at me wasting a whole day trying to figure out what I was missing out on... Darn thing!... :) :)

[codeG]

If you use autofilter then the autosum uses the subtotal function with function number 9 which does not include hidden cells, ie, =subtotal(9,A1:A120)

[tstav]

Hi codeG,
As far as the Autofilter is concerned, it is reported in Help, that the subtotal function ignores (does not include in the calculation) values that are filtered (not visible), regardless of the function_num used.

The above of course, concerns values in a column. We've been wondering about values in a row.

Welcome to the forum.