Create Named Range for each unique value
 

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?

Create Named Range for each unique value
 

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

--
Gary''s Student - gsnu200909


"littleme" wrote:

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?
.