Excel VBA - Find Next Error

[dar149]

Hi,
I have the following code which searches for a partial string. It works fine until it doesn't find a match, then I receive an error. I have it loop until the activecell.value is "End", but because I'm searching for "Total*" it never finds the value to end the loop.

Do Until ActiveCell.Value = "End"
Cells.Find(What:="TOTAL*", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Rows("1:1&quot.EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Loop

Any Suggestions would be greatly appreciated.

 

[dar149]

Hi,
If no one has a solution to this issue, can you think of another way of doing this? I need to search a column for a portion of text, but there are many blanks and formulas also in the column.

Thanks...

 

[Bowers74]

Are you trying to delete every instance of "Total*" in the column?

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Hi dar149,

Find in Excel can be a bit of a pain but to delete all the rows where the cell in column A begins with TOTAL (if that is what you want), I would do something like this ..

With Columns(1)
    .AutoFilter Field:=1, Criteria1:="=TOTAL*"
    .SpecialCells(xlCellTypeVisible).Select
    .EntireRow.Delete
End With

Enjoy,
Tony

 

[Bowers74]

Thanks for jumping in Tony,

After dar149 answered I was going to suggest something like:

Sub DeleteRows()
Dim c As Range
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For Each c In Range("A1:A" & LastRow)
    If c.Value = "Total*" Then
        c.EntireRow.Delete
    End If
Next c
End Sub

But I'm sure your's works just as well (if not better)!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Mike - coupla points I need to pull you up on I'm afraid.

1st is that you need to use c.TEXT rather than c.value
You also need to use "LIKE" rather than "="

2nd Because the code is going down the sheet, if there are any "TOTAL"s next to each other, only the 1st will be deleted because of the disruption to the indexing that occurs. The way to handle deleting rows is to work upwards from the bottom of the sheet eg

For i = lastRow to 1 step -1
if uCase(cells(i,1).text) LIKE "TOTAL*" then rows(i).entirerow.delete
next i

but I would say that the find method or autofilter methods would be quicker. For the find method, I would use:

Sub UseFindEx()
With Range("a1:a" & Cells(65536, 1).End(xlUp).Row)
Set c = .Find("TOTAL", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not c Is Nothing Then
On Error GoTo EndNow
Do
delRow = c.Row
Set c = .FindNext(c)
Rows(delRow).EntireRow.Delete
Loop While Not c Is Nothing
End If
End With
EndNow:
End Sub

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Sorry Geoff,

I guess I didn't pay attention. The code worked the first time I tested it, but when I tested it today (with more than two cells valued as "Total*&quot it left some rows, even though the Range was correct.

I apologize for any inconvenience that I may have caused!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Hay Mike - no need to apologize - it's no inconvenience to me. Jus' tryin' to share the wealth

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Actually the apology was more meant for dar149, in case he tried my code and it screwed something up. It was late and I should have been sleeping instead of thinking!

Have you seen my FAQ? faq68-4037



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[xlbo]

Not 'till just now - looks very good - especially the DATEDIF bit - very useful

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Thanks for the kudos ;-) !

Be on the lookout for more!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[dar149]

The UseFindEx worked perfectly. Thanks to eveyone who replied.