PDA

View Full Version : [SOLVED] Excel 2013>VBA>Function>Average Visible Cells Horizontally



aworthey
06-06-2016, 10:45 AM
Hello,

Is there a way of easily adapting this code to average visible cells horizontally? Thanks!


Function Sum_Visible_Cells(Cells_To_Sum As Object)
Dim cell As Object
Dim Total As Double
Application.Volatile
For Each cell In Cells_To_Sum
If (cell.Rows.Hidden = False) And (cell.Columns.Hidden = False) Then
Total = Total + cell.Value
End If
Next
Sum_Visible_Cells = Total
End Function

aworthey
06-06-2016, 01:50 PM
Here's what I did:


Function AverageVisible(rng As Object)
Dim rCell As Object
Dim iCount As Integer
Dim dTtl As Double

Application.Volatile

iCount = 0
dTtl = 0

For Each rCell In rng
If (rCell.Rows.Hidden = False) And (rCell.Columns.Hidden = False) Then
dTtl = dTtl + rCell
iCount = iCount + 1
End If

Next
AverageVisible = dTtl / iCount

End Function