ExcelBanter - Name ranges based on criteria

Martin, the issue is that you can't have a range named as a number, because
it corresponds to a viable worksheet address (in this case, a row).
Similarly, you can't name a range as a letter combination less than "IV" and
you can't use a cell address (i.e. "A5"). Try naming a range "11" and you
will receive an error. If you want to still accomplish the task, but don't
mind using a slightly different name, this code will do it for you. It looks
through the "TestArea" range and performs the desired action.

Regards,
Bill

Public Sub RenameRanges()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rngMain As Range
Dim rngTemp As Range
Dim strFirst2 As String
Dim strAddress As String
Dim strNamePrefix As String
Dim i As Long

strNamePrefix = "test_"

Set wkb = ThisWorkbook

Set rngMain = wkb.Names("TestArea").RefersToRange
Set wks = rngMain.Parent

For i = 1 To rngMain.Rows.Count
If (Len(rngMain.Cells(i, 1).Value) 0) Then
strAddress = rngMain.Cells(i, 1).Address
strFirst2 = strNamePrefix & Left(rngMain.Cells(i, 1).Value, 2)

If (RangeNameExists(wkb, strFirst2)) Then
Set rngTemp = wkb.Names(strFirst2).RefersToRange
Set rngTemp = Union(rngTemp, wks.Range(strAddress))
wkb.Names(strFirst2).Delete
Call wkb.Names.Add(strFirst2, rngTemp)
Else
Set rngTemp = wks.Range(strAddress)
Call wkb.Names.Add(strFirst2, rngTemp)
End If
End If
Next i
End Sub

Public Function RangeNameExists(wkb As Workbook, strName As String) As Boolean
On Error GoTo ErrHandler

Dim rng As Range
Set rng = wkb.Names(strName).RefersToRange

RangeNameExists = True

Exit Function
ErrHandler:
RangeNameExists = False
End Function

"Martin" wrote:

Hi everybody,

I have a large spreadsheet where I want to name ranges in a macro based on
the first two digits in the cells of one column. In the example below I need
to end up with one named range for the 4 codes starting with 11, one range
for the 3 codes starting with 12 and one range for the 2 codes starting with
13.

110003
110024
110035
110594
120012
120014
120045
130455
131004

Any help much appreciated.
--
Regards,

Martin