Multiple Dynamic Named Ranges w/ Blank Cells Auto Update on Resize

Hi all,

I'm new to the forum and returning to Excel for the first time in approx 15 years. Very rusty w/ basic knowledge.

I currently have a spreadsheet similar to the very simple example attached. I have VBA code as below that forces text in certain ranges to uppercase.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    On Error Resume Next
    If Not Intersect(Target, Range("D5:H19,J5:N19,P5:T19,V5:Z19")) Is Nothing Then
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
    End If
    On Error GoTo 0
    
Application.ScreenUpdating = True

End Sub

However, I would like to have dynamic named ranges so that the code updates when rows/columns are inserted/deleted on the worksheet.

As this spreadsheet is essentially a template, the ranges will begin with all blank cells and even when filled in will still have some blank cells. I currently have the following two formulas that return the correct first and last cells of a range BUT how do I put them together to specify the range itself? If I try to put a colon between them I get an error.

See spreadsheet for more explanation.

CELL("address",OFFSET(Aut_1, 1, 0,))

CELL("address",OFFSET(Oct_HT,COUNTA($B:$B), -1,))

Please ask if any more clarification is needed.

Thank you in advance.