excelvbafunctionhiddensubtotal

Creating a function similar to subtotal, that ignores hidden columns

I am trying to find a replacement for subtotal or aggregate that will sum all cells in a range except those whose column is hidden. Absent the presence of a builtin function I am trying to create my own but am having a lot of difficulty. I have tried the following VBA code but to no avail:


Function VISSUM(myRange As range) As Double
On Error Resume Next

Dim i As Variant
Dim mySum As Integer

For Each i In myRange
    If myRange.SpecialCells(xlCellTypeVisible) = True Then
        mySum = mySum + myRange
    End If
Next i

VISSUM = mySum

End Function

and this didn't work either:


Function mySum(myRange As range) As Double
On Error Resume Next

mySum = Application.WorksheetFunction.Sum(myRange.SpecialCells(xlCellTypeVisible) = True)

End Function

I've also messed around with using myRange.EntireColumn.Hidden = False but no success there. any help would be greatly appreciated.

Solution

  • You could always try:

    i.entirecolumn.hidden = False

    myrange would be checking everything hidden and not hidden, where as I should be the individual range you're dealing with either hidden or not.