Find text - delete the row it's on
Posted by Doug Sloane on July 10, 2001 4:20 AM
Hi
I'm trying to run a macro that opens a large text file, looks for certain text in column A and deletes the row that this text is on. The problem I'm having is that if the text isn't found the macro stops. I've tried several solutions but cant get any of them to work. All help greatly appreciated.
Posted by Dax on July 10, 2001 4:56 AM
Do you mean the code stops with a run-time error? If you post the code I (or someone) should be able to sort it.
Regards,
Dax.
Posted by Doug sloane on July 10, 2001 5:29 AM
Cheers Dax
It's giving me the error message "Object variable or with block variable not set"
While IsEmpty(Selection) = False
Range("A1:A700").Select
Selection.Find(What:="TIMING", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
ActiveCell.EntireRow.Select
Selection.Delete
Selection.Delete
Selection.Delete
Selection.Delete
Selection.Delete
Selection.Delete
Wend
Posted by Dax on July 10, 2001 7:29 AM
How about this?
Sub DeleteRows()
Dim rngeFound As Range, rngeSearchRange As Range
Set rngeSearchRange = ActiveSheet.Range("A1:A700")
Do
Set rngeFound = rngeSearchRange.Find("TIMING")
On Error Resume Next
If rngeFound.Rows.Count = 0 Then Exit Sub
On Error GoTo 0
ActiveCell.EntireRow.Select
rngeFound.Resize(6, 1).EntireRow.Delete
Loop
End Sub
Hope it helps,
Dax.
Posted by Dax on July 10, 2001 7:33 AM
Re: Slight change to code...
You don't need the line
ActiveCell.EntireRow.Select
as I posted. This will avoid the code having to select anything hence speeding it up nicely.
Regards.
Posted by Doug Sloane on July 10, 2001 7:57 AM
Thanks a million
That's great thanks.
