Or this............
Sub CopyNHA()

Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long
Dim dRow As Long
Dim sCount As Long
sCount = 0
dRow = 0

myword = InputBox("Enter items to search for.")

For sRow = 1 To Range("A65536").End(xlUp).Row

If Cells(sRow, "A") Like "*" & myword & "*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
End If
Next sRow

MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"

End Sub

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Don Doan" wrote:

Hi there,
I'm new to macro and I need some help with the program.

Basically, from cell B2 to B25...i want to look for the three character NHA
(in that order). If it's not there, erase the whole row. If it's there, keep
the row. There may be some characters or spaces before and after that word
NHA.
I a few lines in the program. If someone can help me modify it, that would
be great.

Sub Macro1()

Dim k As Long

For k = Cells(25, "b").End(xlUp).Row To 2 Step -1
If Cells(k, "b") < "*NHA*" Then
Rows(k).EntireRow.Delete
End If
Next k

End Sub