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.

 

[Dynex811]

I'm recieving an error that says

Run-time error '1004':
Method 'Range' of object '_Global' failed

And it is pointing to the line

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

But if you're saying that it looks to be working for you then chances are I'm doing something wrong on my end, I'm just not sure what.

 

[Jeffrey Lopez]

Interesting, let me take a look... Odd I had it working.. but though some testing, was only naming the range if there were 2 dups, anymore and it would not work...

 

[Dynex811]

Oh that could be why, I'm working with varying degrees of dups, more often then not there are more than 2. Thank you again for your help!