View Full Version : sum visible cells
lior03
10-05-2007, 05:14 AM
hello
this my version of summing only visible cells in a range:
Function sumonlyvisible(r As Range)
Application.Volatile
Dim cell As Range
For Each cell In r
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
sumonlyvisible = sumonlyvisible + cell.Value
End If
End If
Next
End Function
i wanted to attach this macro to a button and place it along side the excel bulit in sum icon:
On Error Resume Next
Application.CommandBars("standard").Controls("sum visible").Delete
With Application.CommandBars("standard").Controls.Add(before:=24)
.Caption = "sum visible"
.faceid = 308
.Style = msoButtonIcon
.OnAction = ""
.BeginGroup = True
End With
could it be doen?
Bob Phillips
10-05-2007, 05:25 AM
Why not just use SUBTOTAL in Excel?
lior03
10-05-2007, 09:54 AM
i wanted to explore the vba option.
lior03
10-05-2007, 10:07 AM
i tought that by using :
application.volatile
i will not have to use F9 to update my sheet after using my function.it did not happen .why?
thanks
Bob Phillips
10-05-2007, 10:32 AM
You cannot change the worksheet or other cells from a functrion invoked from within a worksheet.
Paul_Hossler
10-09-2007, 06:31 PM
I created a User Defined Function (UDF) from your logic.
As a test, in L1 I put =SumOnlyvisible ("A1:C10") with row 2 hidden.
Function SumOnlyVisible(r As Range) As Double
Dim rCell As Range
Application.Volatile
Dim cell As Range
For Each rCell In r.Cells
With rCell
If Not .Rows.Hidden And Not .Columns.Hidden Then SumOnlyVisible = SumOnlyVisible + .Value
End With
Next
End Function
HTH
Paul
lior03
11-16-2007, 09:04 AM
i am trying togo a little further.i want replace the sum button with the following code:
activecell.FormulaArray = "=SUMONLYVISIBLE()"
does anybody have a suggestion as to how to prevent the need to press f9.
the code of the change is :
CommandBars("רגיל").Controls("סכום אוטומטי").OnAction = "summacomsuper"
thanks
Bob Phillips
11-16-2007, 09:19 AM
Add
Application.Volatile
to the start of the UDF.
Hiding rows does not automatically trigger a recalculation in Excel (unless you are filtering and have Subtotals)
lior03
11-16-2007, 12:46 PM
hello
suppose i found that a button faceid number 226 looks exactly like the excel xp sum button,and suppose even further that i manage to create a udf's function to deal with sum count and average only visible cells.how can i emulate the button as it appear on the standard toolbar so the user is faced upon pressing the button with a possibility to calculate this functions for only visible cells.
thanks
lior03
11-16-2007, 01:51 PM
maybe the button type shoyld be different.instead of a ordinary button a
msoControlButton type it should be a msocontrolbuttonpopup so a dropdown list will appear.
thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.