VBA function subtotal that works for hidden columns

[CDienst]

Hello,

Subtotal apparently only works for filtered data or manually hidden rows. I'd like to make a function in VBA that is basically just subtotal, but work for whatever might be hidden, rather than just rows.

This seems to work as a test

Sub subtest()
   
    Dim cl As Range
    Set cl = Range(Range("h15"), Range("k15")).SpecialCells(xlCellTypeVisible)
    
    MsgBox WorksheetFunction.Subtotal(4, cl)
  
End Sub

In this case, H15:K15 is a list of dates, and I want it to output the most recent visible date

The problem arises when I try to put this in the form of a function.

Function SubCol(x As Integer, y As Range)
    
    Dim cl As Range


    Set cl = Range(Range(y), Range(y).End(xlToRight)).SpecialCells(xlCellTypeVisible)
      SubCol = WorksheetFunction.Subtotal(x, cl)


End Function

I'm sure I must have my formatting wrong to reference the parameter y.

I realize that even with the formatting correct, this might not function entirely as subtotal would since it only works for an input of a single range besides the mode of the subtotal, but for my current purposes it should work fine.

Does anyone know what I need to change in the formatting here and/or a better way to mimic subtotal functionality with the addition of ignoring values in hidden columns?

Thanks

 

[JoeMo]

Hello,

Subtotal apparently only works for filtered data or manually hidden rows. I'd like to make a function in VBA that is basically just subtotal, but work for whatever might be hidden, rather than just rows.

This seems to work as a test

Sub subtest()
   
    Dim cl As Range
    Set cl = Range(Range("h15"), Range("k15")).SpecialCells(xlCellTypeVisible)
    
    MsgBox WorksheetFunction.Subtotal(4, cl)
  
End Sub

In this case, H15:K15 is a list of dates, and I want it to output the most recent visible date

The problem arises when I try to put this in the form of a function.

Function SubCol(x As Integer, y As Range)
    
    Dim cl As Range


    Set cl = Range(Range(y), Range(y).End(xlToRight))[COLOR=#ff0000][B].SpecialCells(xlCellTypeVisible)[/B][/COLOR]
      SubCol = WorksheetFunction.Subtotal(x, cl)


End Function

I'm sure I must have my formatting wrong to reference the parameter y.

I realize that even with the formatting correct, this might not function entirely as subtotal would since it only works for an input of a single range besides the mode of the subtotal, but for my current purposes it should work fine.

Does anyone know what I need to change in the formatting here and/or a better way to mimic subtotal functionality with the addition of ignoring values in hidden columns?

Thanks

I haven't looked closely enough at your function to know if there are any other issues, but at a minimum, SpecialCells cannot be used in a UDF in any version of Excel I'm aware of.

 

[CDienst]

Oh, well I hadn't considered something like that. I did a bit of googling and modified something from a similar situation.

Function SubCol(x As Integer, y As Range)
    SubCol = WorksheetFunction.Subtotal(x, VisibleCells(y))
End Function


Private Function VisibleCells(rng As Range) As Range
    Dim r As Range
    For Each r In rng
        If r.Hidden = False Then
            If VisibleCells Is Nothing Then
                Set VisibleCells = r
            Else
                Set VisibleCells = Union(VisibleCells, r)
            End If
        End If
    Next r
End Function

There aren't any errors, but this outputs #VALUE !