This is a function I use to make valid range names:

Function MakeValidRangeName(ByVal strRangeName As String, _
Optional strAddIfTrailingNumerics As String =
"_") As String

Dim strInvalidChars
Dim i As Byte

If Len(strRangeName) = 0 Then
MakeValidRangeName = "No_Name_Provided"
Exit Function
End If

'delete leading numerics
'-----------------------
strRangeName = ClearLeadingNumerics(strRangeName)

'note that the characters \ and . are valid
'------------------------------------------
strInvalidChars = Array("!", "£", "$", "%", "%", "^", "&", _
"*", "(", ")", "-", "+", "=", "{", _
"}", "[", "]", ":", ";", "@", "'", _
"~", "#", "|", "<", ",", "", "?", _
"/")

strRangeName = Replace(strRangeName, " ", "_", 1, -1, vbBinaryCompare)

For i = 0 To UBound(strInvalidChars)
strRangeName = Replace(strRangeName, _
strInvalidChars(i), _
"", _
1, _
-1, _
vbBinaryCompare)
Next i

'to avoid range names ending with numerics
'-----------------------------------------
If Asc(Right$(strRangeName, 1)) 47 And _
Asc(Right$(strRangeName, 1)) < 58 Then
strRangeName = strRangeName & strAddIfTrailingNumerics
End If

strRangeName = Left$(strRangeName, 255)

MakeValidRangeName = strRangeName

End Function


RBS


"mark kubicki" wrote in message
...
I'm writing a sub that works thru a worksheet adding named ranges for all
of the values in a given column (the values are person's names); and I am
naming these "named ranges" effectively the same as the person's name
(ex: the person's name might be "Tom", so the range is named
"Proj_mngr_Tom")

one of the values person's name has a "-" (Ju-Li) which is causing an
error...

is there away of stripping the "-"? (I am presuming I'll need to write a
sub that cycles thru each letter, checking its value...)

thank in advance
-mark