A
AndreasHermle
Dear Experts:
this macro, courtesy by Ron Rosenfeld ...
.... searches for the string 'Sales' and creates named ranges for the
respective current regions.
The created named ranges have got a workbook-level scope.
How would I have to re-write the macro to get worksheet level names
(local)? Say, the worksheet on which the ranges have to be named is
named 'Charting'
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
Sub Ranges_Create()
'Courtesy by Ron Rosenfeld, Google Groups
Dim c As Range
Dim firstAddress As String
Dim i As Long
i = 1
With Range("A1")
If .Value = "Sales" Then
.CurrentRegion.Name = "range" & i
firstAddress = .Address
i = i + 1
End If
End With
Set c = Cells.Find(What:="Sales", After:=Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
Do While Not c Is Nothing And c.Address <> firstAddress
c.CurrentRegion.Name = "range" & i
i = i + 1
If firstAddress = "" Then firstAddress = c.Address
Set c = Cells.FindNext(c)
Loop
End Sub
this macro, courtesy by Ron Rosenfeld ...
.... searches for the string 'Sales' and creates named ranges for the
respective current regions.
The created named ranges have got a workbook-level scope.
How would I have to re-write the macro to get worksheet level names
(local)? Say, the worksheet on which the ranges have to be named is
named 'Charting'
Help is much appreciated. Thank you very much in advance.
Regards, Andreas
Sub Ranges_Create()
'Courtesy by Ron Rosenfeld, Google Groups
Dim c As Range
Dim firstAddress As String
Dim i As Long
i = 1
With Range("A1")
If .Value = "Sales" Then
.CurrentRegion.Name = "range" & i
firstAddress = .Address
i = i + 1
End If
End With
Set c = Cells.Find(What:="Sales", After:=Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)
Do While Not c Is Nothing And c.Address <> firstAddress
c.CurrentRegion.Name = "range" & i
i = i + 1
If firstAddress = "" Then firstAddress = c.Address
Set c = Cells.FindNext(c)
Loop
End Sub