Option Explicit
Sub CreateDefinedNames()
Dim dicNames As Object
Dim vKey As Variant
Dim rData As Range
Dim sName As String
Dim i As Long
Set dicNames = CreateObject("Scripting.Dictionary")
dicNames.CompareMode = 1
Set rData = Range("A1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For i = 1 To rData.Rows.Count
sName = rData.Cells(i, 1).Value
If Len(sName) > 0 Then
If IsEmpty(dicNames(sName)) Then
Set dicNames(sName) = rData.[COLOR=#ff0000]Cells(i, 2)[/COLOR]
Else
Set dicNames(sName) = Union(dicNames(sName), rData.[COLOR=#ff0000]Cells(i, 2)[/COLOR])
End If
End If
Next i
For Each vKey In dicNames.keys
ActiveWorkbook.Names.Add Name:="rng" & Replace(vKey, " ", "_"), RefersTo:="=" & dicNames(vKey).Address(, , , True)
Next vKey
MsgBox "Completed . . .", vbInformation
End Sub