VBA function: Find Average within Dynamic Range

Hi Forum.

I would like to add a custom VBA function to find an average of a range which is defined as follows:

• The selected range is the lower and upper bound for the function
• Within the selected range it should find the first column that is visible (no hidden columns should be taken into account)
• In the function you should add how many rows you want to add into the new Range start. If it exceeds the upper bound defined in the selected range then use upper bound.

I've attached a picture with an example of how i would like the function to behave.

How do i solve that?

Thank you in advance.

3Ce43Zz.png

Here's the function. However, there's no way we can get the formula to auto-refresh when a column is hidden or unhidden. You would have to recalculate the sheet by pressing Shift + F9 or from the 'Formulas' tab.

Function SpecialAverage(rng1 As Range, x As Long)
    Application.Volatile
    Dim cll As Range
    Dim dblSum As Double
    Dim lngCnt As Long
    For Each cll In rng1.Cells
        If lngCnt = x Then Exit For
        If Not cll.EntireColumn.Hidden Then
            dblSum = dblSum + cll.Value
            lngCnt = lngCnt + 1
        End If
    Next cll
    SpecialAverage = dblSum / lngCnt
End Function