Does this solution for non volatile dynamic named range work?

I have thousands of cells that refer to a dynamic named range. They slow down my workbook tremendously, because the named range is recalculated every time the sheet calculates which forces those thousands of cells to recalculate and each one of those thousands of cells forces the named range to recalculate again.

Exmple:

myRange RefersTo : "=Offset($B$12,1,0,CodesCount,1)"
$C$12 formula : "=CountIf(myRange,$B12)" copied down 10,000+ rows.

The formulas in cells $C$12:$C$10,000+ force myRange to recalculate 10,000+ times.

I thimk I have discovered a way to avoid all the calculations.

myRange RefersTo : "=myOffset($B$12,1,0,CodesCount,1)"

Function myOffset(rAnchor as Range, lRows as Long, lCols as Long, lHeight as Long, lWidth as Long) as Range
    Set myOffset = rAnchor.Offset(lRows, lCols).Resize(lHeight, lWidth)
End Function

Private Sub Worksheet_Calculate()
    With Me
        If .Range("Calculate").Value <> .Range("CodesCount") Then
            .Range("CodesCount").Value = .Range("Calculate").Value
        End If
    End With
End Sub

Range("Calculate") calculates what the value of CodesCount should be and automatically changes the value of CodesCount and forces myRange to recalculate which forces those thousands of cells to recalculate.

The only issue I have with this solution is this:
If I have a 2nd dynamic named range dependent on CodeCount, only 1 of them gets calculated when CodesCount changes.

my2ndRange RefersTo : "=myOffset($C$12,1,0,CodesCount,1)"    - doesn't recalculate automatically

As far as I can tell it's not a problem. Any formula that is dependent on my2ndRange forces it to recalculate, and if I use my2ndRange in a macro, it also forces it to recalculate, but it's unnerving for it not to recalculate automatically.

Before I change all my RefersTos to use my UDF(), does anyone see a problem with this solution?
Also, does anyone have an idea why my2ndRange doesn't automatically recalculate?

You could define that name using non-volatile INDEX rather than the volatile OFFSET.

Name: myRange
RefersTo: =Sheet1!$B$12:INDEX(Sheet1$$B:$B, CodesCount, 1)

That should speed things up, but what is the definition of CodesCount?

I can't use INDEX because the owner of the file has the habit of moving data around to suit him.

=Sheet1!$B$12:INDEX(Sheet1!$B:$B, CodesCount, 1)

When he cuts $B$12:$B$10,000 and pastes it at $D$12:$D$10,000 the name range would then read

=Sheet1!$D$12:INDEX(Sheet1!$B:$B, CodesCount, 1)

which would be a disaster.

In my solution CodesCount is just a number, no formula.

=INDEX(Sheet1!$1:$1048576, 12, 2):INDEX(Sheet1!$1:$1048576, CodesCount, 2)

I'm not sure in what way that would be considered dynamic if CodesCount is a constant ...

You're right. It would not be dynamic if CodeCount is a constant.

CodesCount only changes about once every 3 months. I need to have a few hundred ranges adjust their size and several thousands of formulas recalculate when that happens. I'm trying to find a way to have them all adjust automatically, without having them constantly recalculating with every Worksheet_Calculate().

Shg,
If a non-volatile function has the whole sheet as its precedents, how is that different than a volatile function?

An alternate would be =INDEX(Sheet1!$B:$B, 12, 1):INDEX(Sheet1!$B:$B, CodeCount, 1)

It's different than volatile because it doesn't recalculate in reponse to changes on other sheets, and it doesn't recalculate if you press F9.

Your solution is good unless someone moves all of col B.

Why would it be a problem if the entire column is moved?
When I'm working on the file I move entire columns, but the owner only moves what he can see.

It depends on the behavior you want, which you have not explained. If you moved the entire column, the named range definition would move to follow it.

That's what I want. It never occurred to me that someone might want a different behavior. I anchor the range to the column heading ($B$12), but the data actually starts in $B$13. That way if a row gets inserted below $B$12, the range still starts in $B$13.

I'm curious about your bosses behavior.
It sounds like you want your named range to stick with column B, regardless of what your boss cut/pastes.
Wouldn't he be adding data to the new location, requiring that the name moves with the cells.

P.S. have you asked your boss to stop breaking spreadsheets? Or, perhaps more politic, have you asked him what lay-out he likes so that you can make the master spreadsheet with a lay-out that he doesn't have to keep "fixing".

He's not my boss. He hired me create the formulas that accomplish what he wants done, but he wants to use the file himself. As he thinks of more things he wants it to do, he redesigns the look and feel of it to suit himself.

The named range does move with the data. I just have to make sure that any cells referenced in the range definition are included inside the area that he will move, so I can't use $B:$B, because $B$1 isn't a cell that he will move. Offset accomplishes that, but there are so many offsets it slows down the workbook.

He hasn't broken the spreadsheet for quite a while. As long as I accommodate his desires, everything works fine. Speeding the worksheet up is the last problem for a while. I do wish that he would only move entire columns, but as long as he's paying me, I'll do what he wants.