Create Named Range for each unique value

[littleme]

Hi!

All help is welcome here... Am really stuck.

What I have is a list of room numbers. Many appear multiple times i
column A, but it will always be sorted.

What I need is a way to easily create Named Ranges for each group of
rooms. (ie all rows with value 1 becomes one named range, rows with
value 5 becomes one named range and so on)

Range Name should prefereable be same a room name.

Would like the code to go through column A and create ranges for each
unique value. (could also have a "supporting" sheet with uniqe values
if necessary)

Any takers?

 

[Gary''s Student]

If the values are sorted:

Sub RangeMaker()
Set r = Cells(1, 1)
n = Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = 2 To n
If Cells(i, 1).Value = Cells(i - 1, 1).Value Then
Set r = Union(r, Cells(i, 1))
Else
r.Name = "Name" & Cells(i - 1, 1).Value
Set r = Cells(i, 1)
End If
Next
End Sub