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.

Name	Refers to:

Aut_1	=OFFSET(Sheet1!$A$1,4,MATCH("A1",Sheet1!$A$3:$AA$3,0)-1,COUNTA(Sheet1!$B:$B)-1,SMALL(IF(Sheet1!$D$3:$AA$3="HT",COLUMN(Sheet1!$D$3:$AA$3)),1)-MATCH("a1",Sheet1!$A$3:$AA$3,0))
Aut_2	=OFFSET(Sheet1!$A$1,4,MATCH("A2",Sheet1!$A$3:$AA$3,0)-1,COUNTA(Sheet1!$B:$B)-1,SMALL(IF(Sheet1!$D$3:$AA$3="HT",COLUMN(Sheet1!$D$3:$AA$3)),2)-MATCH("A2",Sheet1!$A$3:$AA$3,0))
Spr_1	=OFFSET(Sheet1!$A$1,4,MATCH("S1",Sheet1!$A$3:$AA$3,0)-1,COUNTA(Sheet1!$B:$B)-1,SMALL(IF(Sheet1!$D$3:$AA$3="HT",COLUMN(Sheet1!$D$3:$AA$3)),3)-MATCH("S1",Sheet1!$A$3:$AA$3,0))
Spr_2	=OFFSET(Sheet1!$A$1,4,MATCH("S2",Sheet1!$A$3:$AA$3,0)-1,COUNTA(Sheet1!$B:$B)-1,SMALL(IF(Sheet1!$D$3:$AA$3="HT",COLUMN(Sheet1!$D$3:$AA$3)),4)-MATCH("S2",Sheet1!$A$3:$AA$3,0))

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Group As Variant
    If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

    For Each Group In Split("Aut_1,Aut_2,Spr_1,Spr_2", ",")
        If Not Intersect(Target, Range(Group)) Is Nothing Then
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
            End With
            Target = UCase(Target)
            Exit For
        End If
    Next Group
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub