Remove Certain Last Words from String

I'm attempting to extract the Street Names from an Address.

For Example, if given "123 Very Big Street" I'd want to extract "Very Big".

If given, "123 Very Big" I'd want the same answer.

I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".

How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.

Thanks,

Shred

Private Sub CommandButton7_Click()
Dim mystr, mynewstr, lastword As String
Dim lastblank, firstblank As Long

mystr = Cells(ActiveCell.Rows.Row, 1).Value
firstblank = InStr(mystr, " ")
lastblank = InStrRev(mystr, " ")

lastword = Right(mystr, Len(mystr) - lastblank)

If firstblank = lastblank Then
    mynewstr = mystr
Else
    If LCase(lastword) = "drive" Or LCase(lastword) = "street" Then
        mynewstr = Mid(mystr, firstblank + 1, lastblank - firstblank - 1)
    Else
        mynewstr = Right(mystr, Len(mystr) - firstblank)
    End If
End If

MsgBox mystr & vbCrLf & vbCrLf & mynewstr
End Sub

What I was getting at is having about 50 or so strings to match against.

Street, Drive, Way, Lane, road, rd., st., Blvd., Avenue, Ave, etc.

Thanks for the reply.

I'm trying to keep the maintenance of the list of lastwords out of the user's hands. I want to keep it in the code. Either way, I have to type the values somewhere.

I was thinking I could somehow populate an array with the values, and then loop through them to see if I have a match with the curent string.

I guess I could hide the worksheet the dynamic range list is on.

Any other ideas?

Thanks,

Shred