Create named range

  • Thread starter Thread starter Robert
  • Start date Start date
R

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!
 
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
 
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
 
Back
Top