Create named range

[Robert]

I have a worksheet with rows of data. Col. A contains a
number. I sort the entire list by Col. A then I want to
create a named range for each change in Col. A.

For example, if Cells' A1, A2, and A3 all contain 999,
then I want to take all the Col. A cells with 999 and
create a named range of "999". Then, if Cells' A4, A5,
A6, and A7 all contain 888, then I want to take those 4
cells and create a named range of "888".

Any suggestions on the most efficent way to perform this
task?

Thanks in advance!

 

[J.E. McGimpsey]

First, "999" and "888" are not valid names. You could prefix or
postfix them with a character as I've done here:

one way:

Public Sub NameRanges()
Dim cell As Range
Dim firstCell As Range
Set firstCell = Range("A1")
For Each cell In Range(firstCell, _
Cells(Rows.Count, 1).End(xlUp))
With cell
If .Offset(1, 0).Value <> .Value Then
Range(firstCell, .Cells).Name = _
Format(.Value, "_000")
Set firstCell = .Offset(1, 0)
End If
End With
Next cell
End Sub

 

[Tom Ogilvy]

Names have to start with a letter and can not resemble a range reference.
Sub Tester1()
Dim Start As Range
Dim cell As Range
Dim vVal As Variant
Set Start = Range("A1")
vVal = Start.Value
For Each cell In Range(Cells(1, 1), _
Cells(1, 1).End(xlDown)(2))
If cell.Value <> vVal Then
Range(Start, cell.Offset(-1, 0)) _
.Name = "Name_" & vVal
Set Start = cell
vVal = Start.Value
End If
Next

End Sub