Build Excel function: Unmerge, calculate, re-merge. Problem: Function starts to run recursive before finishing

Question

My main goal is to be able to autofilter merged cells in one column.
In the picture below I want row 7-9 to disappear when I remove "6" from the autofilter menu.
But as I have figured, I need the value "6" to be held in all the cells "L7:L9" in order for Excel to do so.

The number 6 is calculated by adding "Num1" and "Num2" (2 * 3) by the following function I have placed in "L7":

Function Exposure(arg1 As Range, arg2 As Range) As Variant
Application.EnableEvents = False
Application.Calculation = xlManual

If Application.ThisCell.Offset(, -1).Value <> "-" And Application.ThisCell.Offset(, -2).Value <> "-" Then
       Exposure = Left(Application.ThisCell.Offset(, -1).Value, 1) * Left(Application.ThisCell.Offset(, -2).Value, 1)
End If
If Exposure = 0 Then Exposure = "-"

Application.Calculation = xlAutomatic
Application.EnableEvents = True
End Function

I put the following formula inside the merged cell "L7":=Exposure(K7;J7). Then formula is dragged down.
"Num1" and "Num2" are controlled by valdiation fields, drop-down menu.

My plan was to unmerge after calculating the Exposure Variant, fill the same value in the remaining rows, then re-merge the same area. So I wrote this stand alone Sub:

Sub WorkingSub(rng As Range)
'Set rng = ActiveCell.MergeArea
rng.UnMerge
For i = 2 To rng.Cells.Count
    rng.Cells(i).Value = rng.Cells(1).Value 'This line triggers recursion
Next i
rng.Offset(rng.Cells.Count).Copy 'Copies format from below
rng.PasteSpecial Paste:=xlPasteFormats 'Paste that keeps the values even after merging
End Sub

Which works on its own, but not when called inside the function above. After setting the first value, the function triggers "something", debug show the the function starting over, skipping the rng.PasteSpecial Paste:=xlPasteFormats code.

So my question to you guys is how do i write my function(s) to stop "recursing" and let me unmerge during the function call?

Or am I attacking this the wrong way? What would you do?

I am stuck with merged cells for lots of reasons, this is just one part of many inside this spreadsheet.

Answer 1

An interesting problem. You can capture the filter event through trapping a change in a calculation and then processing the rows of the table for visibility. I've made some assumptions for the initial table range assignment which may need some alteration.

The If Not VisRange Is Nothing Then is actually redundant as the prior line will throw a fit if an empty range is assigned, but I just kept it in. In order to get around having a null range, keep the header range in the initial MergedTableRange so there will always be a row visible

Within a cell either somewhere in the same worksheet or a 'dummy' worksheet

=SUBTOTAL(103,Sheet1!A3:H10) 'Or other table range

In the worksheet module code

Private Sub Worksheet_Calculate()
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim MergedTableRange As Range: Set MergedTableRange = ws.Range("A2").CurrentRegion
    
    Dim Cell As Range
    Dim VisRange As Range: Set VisRange = MergedTableRange.SpecialCells(xlCellTypeVisible)
    If Not VisRange Is Nothing Then
        For Each Cell In VisRange
            If Not Application.Intersect(Cell.MergeArea, VisRange).Address = Cell.MergeArea.Address Then
                Cell.Rows.Hidden = True
            End If
        Next Cell
    End If
End Sub

Answer 2

I came up with a different approach. Maybe there's a downside I'm missing. But my few test runs have succeeded.

I allready have a hidden sheet named "Template" where the formats for each new "#" is stored. So whenever the user wants to insert a new row, the template have the merged and the non-merged cells ready and insert is done through copy paste.

In that same sheet I made 2 merged rows in column 2, 3 merged cells in column 3 and so on:

This way I'm able to copy the correct number of merged rows to paste after filling the unmerged rows with their correct values.

I came to the conclusion that I could catch a Worksheet_change on the "Num1" and "Num2" columns instead of catching and canceling an autofilter call.

So I added:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("J:J")) Is Nothing Then
        Call UnMergeMerge(Cells(Target.Row, "L").MergeArea)
End If
If Not Intersect(Target, Target.Worksheet.Range("K:K")) Is Nothing Then
        Call UnMergeMerge(Cells(Target.Row, "L").MergeArea)
End If
End Sub

And the UnMergeMerge sub ended up being:

Sub UnMergeMerge(rng As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
rng.UnMerge
For i = 2 To rng.Cells.Count
    rng.Cells(i).Value = rng.Cells(1).Value
Next i
With Sheets("Template")
    .Range(.Cells(8, rng.Cells.Count), .Cells(8 + rng.Cells.Count, rng.Cells.Count)).Copy
End With
rng.PasteSpecial Paste:=xlPasteFormats
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Still not sure it's the fastest and best approach...Do you guys still believe catching, undoing and running a different autofilter would be more effective?