Avatar of Seamus2626
Seamus2626
Flag 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
Microsoft Excel

Avatar of undefined
Last Comment
Seamus2626

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Seamus2626

ASKER
Thanks ;)
Guy Hengel [angelIII / a3]

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

Seamus2626

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Guy Hengel [angelIII / a3]

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

Seamus2626

ASKER
Thanks  angelIII :-)