VBA: Multiple Named Ranges with Offset

Question

I have the following code

Sub NamedRange()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim rangeC1F As Range
Dim rangeC2F As Range
Dim rangeC3F As Range
Dim rangeC4F As Range
Dim rangeP1F As Range
Dim rangeP2F As Range
Dim rangeP3F As Range
Dim rangeP4F As Range

    For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    With ActiveWindow
        Set rangeC1F = ws.Range("G3:G100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_C1F", " ", "_"), RefersTo:=rangeC1F
        Set rangeC2F = ws.Range("T3:T100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_C2F", " ", "_"), RefersTo:=rangeC2F
        Set rangeC3F = ws.Range("AG3:AG100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_C3F", " ", "_"), RefersTo:=rangeC3F
        Set rangeC4F = ws.Range("AT3:AT100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_C4F", " ", "_"), RefersTo:=rangeC4F
        Set rangeP1F = ws.Range("BG3:BG100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_P1F", " ", "_"), RefersTo:=rangeP1F
        Set rangeP2F = ws.Range("BT3:BT100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_P2F", " ", "_"), RefersTo:=rangeP2F
        Set rangeP3F = ws.Range("CG3:CG100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_P3F", " ", "_"), RefersTo:=rangeP3F
        Set rangeP4F = ws.Range("CT3:CT100")
        ActiveWorkbook.Names.Add Name:=Replace(ws.Name & "_P4F", " ", "_"), RefersTo:=rangeP4F
    End With
Next                                               
Application.ScreenUpdating = True                                         
End Sub

This code enables me to create defined/named ranges for a large amount of sheets. However, with the current code the ranges are static. I would like to make them dynamic with the "offset"-function. The lenght of the ranges are determined by the values of cell F1 for range C1F, cell S1 for range C2F, cell AF1 for range C3F, cell AS1 for range C4F, cell BF1 for range P1F, cell BS1 for range P2F, cell CF1 for range P3F, cell CS1 for range P4F. The starting cells remain the same (cells G3, T3, AG3, ..., CT3)

How can I correctly insert offset function in my existing module?

thank you!

user8053786 user8053786 · Accepted Answer · 2017-05-24T13:10:36

The first one, range C1F, can be changed to:

ws.Names.Add Name:=Replace(ws.Name & "_C1F", " ", "_"), RefersToR1C1:="=OFFSET(R3C7,0,0,R1C6,1)"

And then you change the rest accordingly.

Alternatively, if you want the ranges to change every time you run the code:

ws.Names.Add Name:=Replace(ws.Name & "_C1F", " ", "_"), RefersToR1C1:="=OFFSET(R3C7,0,0,” & RowLength & " ,1)"

Where you make

RowLength=range(“F1”).value

VBA: Multiple Named Ranges with Offset

2 Answers