Link to home
Start Free TrialLog in
Avatar of Seamus2626
Seamus2626Flag for Ireland

asked on

Find with an OR

Hi,

I have a find piece of code as follows;

With ActiveSheet.Columns("K:K")
    Set rFind = .Find(What:="Corporate Actions", After:=.Cells(.Rows.Count, 1), LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        .Range(.Cells(1, 1), rFind.Offset(-1)).EntireRow.Delete
    End If
End With

I would like to change so that it can find "Corporate Actions" or "Corp Action"

Thanks
Seamus
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Seamus2626

ASKER

Thanks ;)
in short, I would create a procedure like this:

private sub DeleteRowsByPattern(_sheet as variant, _area_to_look as string, _pattern as string )

With _sheet.Columns(_area_to_look)
    Set rFind = .Find(What:=_pattern, After:=.Cells(.Rows.Count, 1), LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        .Range(.Cells(1, 1), rFind.Offset(-1)).EntireRow.Delete
    End If
End With

End sub 

Open in new window


and call that like this:
DeleteRowsByPattern ActiveSheet, "K:K", "Corp Action"
DeleteRowsByPattern ActiveSheet, "K:K", "Corporate Actions"

Open in new window

Hey, i attempted to place the above procedure in a module and i got a all red on lines


private sub DeleteRowsByPattern(_sheet as variant, _area_to_look as string, _pattern as string )

With _sheet.Columns(_area_to_look)
    Set rFind = .Find(What:=_pattern, After:=.Cells(.Rows.Count, 1), LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

Its pointing at the underscores as the problem

Where should i place the procedure?

Thanks
Seamus
if it's into a module, you need to put public.

and sorry, _ is indeed not allowed in VBA  at that place, my fault

public  sub DeleteRowsByPattern(the_sheet as variant, the_area_to_look as string, the_pattern as string )

With the_sheet.Columns(the_area_to_look)
    Set rFind = .Find(What:=the_pattern, After:=.Cells(.Rows.Count, 1), LookAt:=xlWhole, _
                      SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        .Range(.Cells(1, 1), rFind.Offset(-1)).EntireRow.Delete
    End If
End With

End sub 
                                            

Open in new window

Thanks  angelIII :-)