Name range based on cell contents

ravery

New Member
Joined
Feb 28, 2005
Messages
21
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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Feb 28, 2005
Messages
21
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

New Member
Joined
Feb 28, 2005
Messages
21

ADVERTISEMENT

Thanks Damon!

I will give it a try

Ray
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top