FindNext

[SJ]

I am trying to find text in my file then delete the entire row then find the next occurance of the same text, but my code is getting stuck on the findnext line. This is my code. Can you help me

Sub testcell3(

Range("A6").Selec

With ActiveSheet.Range("a6:I1000"
Set P = .Find(what:="PAYMENT RECEIVED THANK YOU", after:=ActiveCell, LookIn
:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=
xlNext, MatchCase:=False
If Not P Is Nothing The
FirstAddress = P.Addres
D
P.EntireRow.Delete Set P = .FindNext(P
Loop While Not P Is Nothing And P.Address <> FirstAddres
End I
End Wit

End su

 

[Greg Wilson]

First, my assumption was that the inclusion of the
line "Set P = .FindNext(P)" on the same line
as "P.EntireRow.Delete" was website wordwrap and is not
listed like that in your code.

I think the problem was that by deleting a row while the
macro is operational screws up the referencing of the Find
method. I suggest that you instead assign each found cell
to a noncontiguous "DeleteRng" range variable. Then
delete this range at the very end thus avoiding this
complication.

Also, if I'm not mistaken, you should be using xlValues
instead of xlFormulas for the Lookin parameter. However,
it seems to work both ways for me.


Suggested code:-

Sub testcell3()
Dim P As Range, FirstAddress As String
Dim DeleteRng As Range
With ActiveSheet.Range("A6:I1000")
Set P = .Find(What:="PAYMENT RECEIVED THANK YOU", _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not P Is Nothing Then
FirstAddress = P.Address
Set DeleteRng = P
Do
Set P = .FindNext(P)
Set DeleteRng = Union(DeleteRng, P)
Loop While Not P Is Nothing And _
P.Address <> FirstAddress
End If
End With
DeleteRng.EntireRow.Delete
End Sub

Regards,
Greg

-----Original Message-----
I am trying to find text in my file then delete the

entire row then find the next occurance of the same text,
but my code is getting stuck on the findnext line. This
is my code. Can you help me?

Sub testcell3()

Range("A6").Select

With ActiveSheet.Range("a6:I1000")
Set P = .Find(what:="PAYMENT RECEIVED THANK YOU", after:=ActiveCell, LookIn _
:=xlFormulas, LookAt:=xlPart,

SearchOrder:=xlByRows, SearchDirection:= _

 

[Greg Wilson]

Note that my code will error if there are mutiple
instances of the text "PAYMENT RECEIVED THANK YOU" in the
same row. I didn't think this very likely. It'll have to
be fixed if this is possible.

Regards,
Greg

 

[SJ]

Thanks Greg, thats excellent. But at the last line "DeleteRng.EntireRow.Delete" I get RunTime Error 91 "Object variable or With Block variable not set"

What do you think?

 

[Greg Wilson]

Probalbly because I misspelled the search text. Most
likely I changed the number of spaces between "PAYMENT
RECEIVED" and "THANK YOU". Ensure that the it is spelled
exactly the same as what you have on your worksheet (i.e.
copy and paste it to be sure). My trials worked unless
the search string occurred more than once in the same row.

I made a slight change involving assigning the search text
to a string variable and using this variable as the What
parameter. I also changed the LookIn parameter to
xlValues like I mentioned.

Sub testcell3()
Dim P As Range, FirstAddress As String
Dim Txt As String, DeleteRng As Range
Txt = "PAYMENT RECEIVED THANK YOU"
With ActiveSheet.Range("A6:I1000")
Set P = .Find(What:=Txt, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not P Is Nothing Then
FirstAddress = P.Address
Set DeleteRng = P
Do
Set P = .FindNext(P)
Set DeleteRng = Union(DeleteRng, P)
Loop While Not P Is Nothing And _
P.Address <> FirstAddress
End If
End With
DeleteRng.EntireRow.Delete
End Sub

-----Original Message-----
Thanks Greg, thats excellent. But at the last

line "DeleteRng.EntireRow.Delete" I get RunTime Error
91 "Object variable or With Block variable not set".

 

[SJ]

There are not multiple instances in the same row, just in the same column.

Thanks
SJ

 

[Greg Wilson]

SJ,

Is the code not working when you fix the spelling of the
search text? It's working at my end. Also, is the search
text potentially listed in more than one column; i.e., is
it necessary to specify the search range as A6:I1000
instead of just, say, A6:A1000?

You should include error trapping in case the search text
isn't found. Suggested is "On Error Resume Next" just
before the line "With ActiveSheet.Range("A1:I100")".

 

[SJ]

Thank you very much Greg. It worked with the extra space in the text

I really appreciate the time you spent helping me

Regard
SJ