This should be close...

Dim rngFound As Range
Dim strFirst As Range
Dim rngToSearch As Range

Set rngToSearch = Range("F:F")
Set rngFound = rngToSearch.Find(what:="Vendor ID", _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Nothing Found"
Else
strFirst = rngFound.Address
Do
With rngFound
.ClearContents
.Offset(1, 0).Copy Destination:=Range(.Offset(1, 0), _
.Offset(1, 0).End(xlDown))
End With
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
End If
--
HTH...

Jim Thomlinson


"Christina" wrote:

Sorry I have to post a new thread. I have not been able to do it the way I've
been told.
I dont know VBA or programming. I make macro by recording steps.
I have this macro which ends with a loop as below. It works, but I need it
to End when there are no more cells with VENDOR ID.
It does the step but ends with the dialog box and I have to click end.

Grateful for help


Do
Range("F:F").Find(What:="Vendor ID", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlDown, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
ActiveCell.ClearContents
ActiveCell.Offset(1, 0).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select

Loop

End Sub