Can you Automatically Create Named Ranges based on adjacent cell values?

[Dynex811]

Hi there, I'm going to start off by saying that I am pretty new to excel in general and wholly inexperienced with VBA. That being said, I was wondering if there was a macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.

For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro (I'm assuming that would be what is required but if not please tell me another way) do this for every row even if the word and/or the number changes.

I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.

I'm sorry if this doesn't make sense, if I can clarify I would be happy to do so. And if this question has already been answered (I looked but I couldn't find one exactly the same) then I'm sorry for the repost and please point me too the thread.

 

[Jeffrey Lopez]

There is a way, the only issue I see running into is the States like New Mexico or North Dakota... you can't have spaces for those named ranges, how would you handle those?

 

[JoeMo]

however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F

You can't use the same name (e.g. FLORIDA6325) for different ranges on the same sheet.

 

[Dynex811]

There is a way, the only issue I see running into is the States like New Mexico or North Dakota... you can't have spaces for those named ranges, how would you handle those?

They could just be NEWMEXICO. I could even go in and delete the spaces if that made it easier.

You can't use the same name (e.g. FLORIDA6325) for different ranges on the same sheet.

I'm sorry, I didn't explain well enough. I'm not trying to make them multiple ranges with the same name, I'm trying to include all of them into the same range. So if row 4520 and 4521 both had column C as Florida, and column D as 6325, then both rows would have column F be in the single range FLORIDA6325. I've been grouping them manually that way so far. I'm sorry if that still doesn't make sense.

 

[Jeffrey Lopez]

Well here is what I came up with... it does not account for the "double names" but will for the individual... so you will need to remove the spaces, I do have an idea for the duplicate names, just not sure I can get it to work its way in... but here is what I have so far...

PLEASE BACK UP YOUR EXCEL FILE BEFORE RUNNING THIS MACRO

Sub NamedRanges()


Dim rngCell As Range
Dim intLstRow As Integer


intLstRow = ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("F2:F" & intLstRow)


ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet1!$F$" & rngCell.Row)


Next


End Sub

PLEASE BACK UP YOUR EXCEL FILE BEFORE RUNNING THIS MACRO

 

[s hal]

Maybe I'm oversimplifying this, but is there a reason you can't put the formula =Cn&Dn (where n is the row you're starting in - I'd assume row 2) in cell F for each row and fill it down the spreadsheet? Then sort your spreadsheet by column F. Otherwise I'm really not following what you're doing here with the named range mentions.

 

[Dynex811]

Well here is what I came up with... it does not account for the "double names" but will for the individual... so you will need to remove the spaces, I do have an idea for the duplicate names, just not sure I can get it to work its way in... but here is what I have so far...

PLEASE BACK UP YOUR EXCEL FILE BEFORE RUNNING THIS MACRO

Sub NamedRanges()


Dim rngCell As Range
Dim intLstRow As Integer


intLstRow = ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("F2:F" & intLstRow)


ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet1!$F$" & rngCell.Row)


Next


End Sub

PLEASE BACK UP YOUR EXCEL FILE BEFORE RUNNING THIS MACRO

Thank you, I'm actually leaving work right now so I'll try it first thing tomorrow and let you know how it goes!

 

[Jeffrey Lopez]

Same here, here is what I have so far. It is pretty close, just need to do a little more testing.

Sub NamedRanges()


Dim rngCell As Range
Dim intLstRow As Integer
Dim rcounter As Long


intLstRow = ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("F2:F" & intLstRow)
rcounter = 0


If rngCell.Offset(rcounter, -3).Value & rngCell.Offset(rcounter, -2).Value = rngCell.Offset(rcounter + 1, -3).Value & rngCell.Offset(rounter + 1, -2).Value Then


Do Until rngCell.Offset(rcounter, -3).Value & rngCell.Offset(rcounter, -2).Value <> rngCell.Offset(rcounter + rcounter, -3).Value & rngCell.Offset(rcounter + rcounter, -2).Value
rcounter = rcounter + 1
Loop


ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet1!$F$" & rngCell.Row & ":$F$" & rngCell.Row + rcounter)


Else
End If


ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet1!$F$" & rngCell.Row)


Next
End Sub

 

[Dynex811]

Same here, here is what I have so far. It is pretty close, just need to do a little more testing.

Sub NamedRanges()


Dim rngCell As Range
Dim intLstRow As Integer
Dim rcounter As Long


intLstRow = ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("F2:F" & intLstRow)
rcounter = 0


If rngCell.Offset(rcounter, -3).Value & rngCell.Offset(rcounter, -2).Value = rngCell.Offset(rcounter + 1, -3).Value & rngCell.Offset(rounter + 1, -2).Value Then


Do Until rngCell.Offset(rcounter, -3).Value & rngCell.Offset(rcounter, -2).Value <> rngCell.Offset(rcounter + rcounter, -3).Value & rngCell.Offset(rcounter + rcounter, -2).Value
rcounter = rcounter + 1
Loop


ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet1!$F$" & rngCell.Row & ":$F$" & rngCell.Row + rcounter)


Else
End If


ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet1!$F$" & rngCell.Row)


Next
End Sub

Hi, I wanna say thank you so much for your help so far! I ran the second macro and it almost worked perfectly. The only issue is that it doesn't include the duplicates into a single named range (which I think is what you said in the post with the first macro), and it instead picks the lowest row of the duplicates and names that cell the named range.

 

[Jeffrey Lopez]

Yeah, I noticed that, been working on this one this morning as it seems like a handy bas to have... give this one a whirl... seems to be working (with the range increasing for duplicates, but still needs more testing..

Sub NamedRanges()


Dim rngCell As Range
Dim intLstRow As Integer
Dim rcounter As Long

intLstRow = ActiveSheet.UsedRange.Rows.Count

For Each rngCell In Range("F2:F" & intLstRow)

If rngCell.Offset(rcounter, -3).Value & rngCell.Offset(rcounter, -2).Value = rngCell.Offset(rcounter - 1, -3).Value & rngCell.Offset(rounter - 1, -2).Value Then

Do Until rngCell.Offset(rcounter, -3).Value & rngCell.Offset(rcounter, -2).Value <> rngCell.Offset(rcounter - 1, -3).Value & rngCell.Offset(rcounter - 1, -2).Value
rcounter = rcounter + 1
Loop

ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet3!$F$" & rngCell.Row & ":$F$" & rngCell.Row - rcounter)

GoTo NextLoop:
Else
rcounter = 0

End If

ActiveWorkbook.Names.Add Name:=rngCell.Offset(0, -3).Value & rngCell.Offset(0, -2).Value, RefersTo:=Range("Sheet3!$F$" & rngCell.Row)

NextLoop:
Next

End Sub