Hi Howard,

Am Sat, 4 Jun 2016 03:55:47 -0700 (PDT) schrieb L. Howard:

Your Sub Test() returns all cities in column A, where I need only the cities of the state selected in H1.

sorry, my bad.
Try:

Sub my_Instr_Copy()
Dim rngC As Range
Dim aState As String
Dim aCity As String

aState = Cells(1, 8)

With Sheets("Sheet1")
aCity = Mid(aState, InStr(aState, " - ") + 3, 99)

For Each rngC In .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
If InStr(rngC, aState) Then
Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp)(2) = _
Replace(rngC, aState & " - ", "")
End If
Next

End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016