Help With Error Function

[Quintios]

I have a macro that runs through one column checking for specific values. When it discovers the value, it deletes the row. The problem I have is that when it can't find the value it errors out. If I use On Error Resume Next it ends up deleting lines that I don't want deleted.

Here's the code:

    Cells.Find(What:="STRING", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Selection.EntireRow.Delete

How do I take care of the error in the 'find' statement and skip over the Row Delete if the string is not found?
Onwards,

Q-

 

[LittleSmudge]

Don't resume NEXT
After the line
Selection.EntireRow.Delete
put in a Label and the No Error Resume AtTheLabel

Will that fix your problem?


G LS

 

[Quintios]

I'm not sure I understand, you mean something like this?

On Error Goto Label

Cells.Find...
Selection.EntireRow.Delete

Label:
No Error Resume

I'm not sure what you meant, but this doesn't work. Also, I have many 'Find' commands. The code looks like this:

Cells.Find(What:="Search1...
Selection.EntireRow.Delete

Cells.Find(What:="Search2...
Selection.EntireRow.Delete

Cells.Find(What:="Search3...
Selection.EntireRow.Delete

Cells.Find(What:="Search4...
Selection.EntireRow.Delete

I need something that will skip over the Cells.Find command *and* the row delete if the search item isn't there and then resume the searching and deleting.
Onwards,

Q-

 

[Quintios]

OK, anyone know how to do this? My question has not been answered yet.
Onwards,

Q-

 

[Loomah]

Copied from the help files and adapted. Not tested so you may still have a little work to do!!

With Worksheets(1).Range("a1:a500&quot 'PUT YOUR OWN RANGE ETC. HERE
Set c = Find(What:="STRING", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Delete
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

 

[Quintios]

I get an error on the term 'find', as 'Sub or Function not defined'. I checked the references and Microsoft Excel 8.0 Object Libaray is checked (this is Excel 97 I'm working with.)

Any suggestions? Where did you find the code in the help file?
Onwards,

Q-

 

[Quintios]

Figured it out. It should be .find instead of just find.
Onwards,

Q-

 

[Quintios]

Here's the final code:

Private Sub Del_Row_Headers(strSearch)
'
' Del_Row_Headers Macro
'
Dim c, FirstAddress

On Error GoTo Done

    Range("A1").Select

    With Worksheets(2).Range("A1:A500") 'PUT YOUR OWN RANGE ETC. HERE
        FirstAddress = "$A$1"
        Set c = .Find(What:=strSearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False)
        If Not c Is Nothing And FirstAddress <> c.Address Then
            c.EntireRow.Delete
            FirstAddress = c.Address
        End If
    End With

Done:
Exit Sub
    
End Sub

The problem I was having was that the way the original macro was set up it will always delete a row no matter what, regardless if the search string was found. I don't know why, but I got this code to work. It takes a value from the main sub, the search string, and searches for it starting from the top. In my case there will only be one instance of each string. If the string is not found it hops out of the loop and goes back to the main program.

Thanks for your help!!
Onwards,

Q-