Try this ass an example and if it works as you require adapt to your
needs:

crrow = 1 'row# ofr criterion
crcol = 1 'column# of criterion
dsrow = 3 'start row# of data
derow = 20 'end row# of data
dacol = 1 'column# of data
resrow = 1 'first row# of result
rescol = 5 'column# of result
sstr = Cells(crrow, crcol).Value

k = resrow

For i = resrow To resrow + derow - dsrow
Cells(i, rescol).Value = ""
Next i
For i = dsrow To derow
dastr = Cells(i, dacol).Value
If InStr(1, dastr, sstr, 1) 0 Then
Cells(k, rescol).Value = dastr
k = k + 1
End If
Next i

smith Mary smith
Ron Smith
Mary smith blacksmith
Peter Sellers smith
Rod Stuart
Roy black
Ron Smith
Gordon hunter
blacksmith


Hans