CODEWITHSUNDEEP
excelvba
Dergyll
Dergyll

Reputation: 949

How to change reference of named range?

I want to move the row reference of a named range by the result of an operation, for example: Sheets("Data").Range("Range1").Offset(a-b, 0)

What is the syntax I must use to successfully change: Range1=$A$100 to Range1=$A$(100+(a-b))?

Appreciate any help

Edit: Sure Scott, I am trying to reset a RowMarker named range back to it's original value but can't get the syntax correct:

Above this sub, I have this:

Public lngRowData As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range
Set rngData = ThisWorkbook.Names("RowMarker").RefersToRange
If lngRowData = 0 Then
lngRowData = rngData.Row
    Exit Sub
End If
If rngData.Row = lngRowData Then Exit Sub
If rngData.Row < lngRowData Then
    MsgBox lngRowData - rngData.Row & " rows removed, repairing formulas"
Else
    MsgBox rngData.Row - lngRowData & " rows added, repairing formulas"
End If
lngRowData = rngData.Row

End Sub

I will insert it into the if statements below so that the result between lngRowData and rngData.row becomes the offset.

Thank you guys

Upvotes: 0

Views: 3695

Answers (2)

SJR
SJR

Reputation: 23081

I've always found RefersToRange rather a faff so here is another approach.

Sub x()

Dim a As Long, b As Long

a = 50
b = 10

With Sheets("Data")
    .Range("A100").Name = "Range1"
    Debug.Print Range("Range1").Address 'A100
    .Range("Range1").Offset(a - b, 0).Name = "Range1"
    Debug.Print Range("Range1").Address 'A140
End With

End Sub

Upvotes: 1

CLR
CLR

Reputation: 12279

This would do it:

ActiveWorkbook.Names.Item("Range1").RefersTo = "=Data!" & Range("Range1").Offset(a - b, 0).Address

Upvotes: 2

Related Questions