Name range based on cell contents

[ravery]

First time post....long time reader.
I am looking for help with VBA in naming a range based on the contents of cells. That is I want to look in column B and for all distinct values and name them (range name 140, 160, etc.) The number of rows in the range needs to be variable. For example:

Temperature 140 -0.63 236.629 22.26
Temperature 140 -1.759 235.5 19.77
Temperature 140 -2.759 234.5 19.77
Temperature 140 -3.759 233.5 19.77
Temperature 140 -4.759 232.5 19.77
Temperature 140 -5.759 231.5 19.75
Temperature 140 -6.759 230.5 19.66
Temperature 140 -7.759 229.5 19.57
Temperature 140 -8.759 228.5 m
Temperature 140 -9.759 227.5 m
Temperature 140 -10.759 226.5 m
Temperature 140 -11.759 225.5 m
Temperature 140 -12.759 224.5 m
Temperature 140 -13.759 223.5 m
Temperature 162 -0.588 236.587 27.12
Temperature 162 -1.676 235.5 26.59
Temperature 162 -2.676 234.5 26.59
Temperature 162 -3.676 233.5 26.58
Temperature 162 -4.676 232.5 25.87
Temperature 162 -5.676 231.5 23.32
Temperature 162 -6.676 230.5 19.28
Temperature 162 -7.676 229.5 14.43
Temperature 162 -8.676 228.5 m
Temperature 162 -9.676 227.5 m
Temperature 162 -10.676 226.5 m
Temperature 162 -11.676 225.5 m

Thanks in advance for any help you could provide.

Ray

 

[Damon Ostrander]

Hi Ray,

I'm a bit uncertain as to what are the ranges that you want to name. In your example I assume the temperature values (140, 160, etc.) are in column B. Do you want the range B1:B14 named "140", and the range B15:B26 named "162"? Should the ranges include the other three (or 4) columns?

Damon

 

[ravery]

Thanks for your reply!
Actually the 140 is a day value. I want all rows with column b value=140 to be included into a range named 140 or range1. Likewise all rows with column b values=162 to be named 162 or range 2. Yes I would like to include the other 4 columns.

 

[Damon Ostrander]

Hi again ray,

I believe this code does it:

Sub NameRanges()
Dim StartRow As Long
Dim EndRow As Long
StartRow = 1
EndRow = 1
Do
EndRow = EndRow + 1
If Cells(EndRow, 2) <> Cells(StartRow, 2) Then
'name range
With Range(Cells(StartRow, 1), Cells(EndRow - 1, 5))
Names.Add "Day" & Cells(StartRow, 2), "='" & .Worksheet.Name & "'!" & .Address
End With
StartRow = EndRow
End If
Loop Until IsEmpty(Cells(EndRow, 2))
End Sub

Damon

 

[ravery]

Thanks Damon!

I will give it a try

Ray

 

[ravery]

Works perfectly!

I really appreciate it.

I love this board!

 

[Damon Ostrander]

De nada, Ray.



Damon