autofilter more than 2 with wildcard

[rfletcher35]

Hi,

I have tried to look to see if this has been solved but could not find so apologies if it is there.

This is the code I am using to filter on 2 criteria

'Filter for Diary / Calendar

    ActiveSheet.Range("$A$1:$G$90").AutoFilter Field:=3, Criteria1:="*Diary*", Operator:=xlOr, Criteria2:="*Calendar*"
   
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

However I am repeating myself several time as I have to do this a number of times in my workbook looking for relevant criteria, I have tried the following if this makes sense.

ActiveSheet.Range("$A$1:$G$10000").AutoFilter Field:=3, _
                                 Criteria1:=Array("*Inbox*", "*Whitelist*", "*Diary*", "*Whitelist*", "*Inbox*"), _
                                 Operator:=xlFilterValues

The above only works with again 2 criteria but I need to have several in there so I can condense my code.

Can anyone please help with this.

Many Thanks

Fletch

 

[vcoolio]

Hello Fletch,

Just going back to the code in post #3, to delete the rows after transfer, just add another line of code as follows:-

With ws.Range("C1", Range("C" & Rows.Count).End(xlUp))
                    .AutoFilter 1, "*" & ar(i) & "*"
                    .Offset(1).EntireRow.Copy Sheets("Somewhere").Range("A" & Rows.Count).End(3)(2)
                    .Offset(1).EntireRow.Delete
                    .Autofilter
 End With

There are a number of criteria that you have placed in the array(s) which are duplicates. You only need one.
However, as is Peter, I'm a little confused but if it's working for you.......................

BTW, again referring to post #3, I neglected to include a line to turn off the AutoFilter so just refer to the code above and you'll see .Autofilter just above End With.
That will turn off the Autofilter.

Cheerio,
vcoolio.

 

[rfletcher35]

Are you sure that is even close to what you are wanting? For me, it is resulting in what I would have thought are strange results on some of the destination sheets, including hidden rows.

BTW, about how many rows in the original sheet? Is it anything like the 100,000 rows suggested by

It did seem a little hit and miss but since adding .autofilter below the offset line on the last couple of runs it has worked. It was a bit stranger with hiding rows I noticed. However I still require a way to delete the selections as at the end of my code whatever is left following the filtering goes into the "Other" worksheet. Can you help?

Thanks

Fletch

 

[rfletcher35]

Hello Fletch,

Just going back to the code in post #3, to delete the rows after transfer, just add another line of code as follows:-

With ws.Range("C1", Range("C" & Rows.Count).End(xlUp))
                    .AutoFilter 1, "*" & ar(i) & "*"
                    .Offset(1).EntireRow.Copy Sheets("Somewhere").Range("A" & Rows.Count).End(3)(2)
                    .Offset(1).EntireRow.Delete
                    .Autofilter
 End With

There are a number of criteria that you have placed in the array(s) which are duplicates. You only need one.
However, as is Peter, I'm a little confused but if it's working for you.......................

BTW, again referring to post #3, I neglected to include a line to turn off the AutoFilter so just refer to the code above and you'll see .Autofilter just above End With.
That will turn off the Autofilter.

Cheerio,
vcoolio.

Thanks following the adding of the .autofilter this seems to have stopped the random hidden rows and it not copying anything. However I can't seem to find the answer to deleting whatever it selects following the copy as whatever is left is then placed in "Other" worksheet for analysis. I have posed the question, did you say this has been answered?

 

[rfletcher35]

Are you sure that is even close to what you are wanting? For me, it is resulting in what I would have thought are strange results on some of the destination sheets, including hidden rows.

BTW, about how many rows in the original sheet? Is it anything like the 100,000 rows suggested by

Hi Peter,

I just put that figure down to cover but I'm sure there is a neater way for this.

Fletch

 

[rfletcher35]

Thanks following the adding of the .autofilter this seems to have stopped the random hidden rows and it not copying anything. However I can't seem to find the answer to deleting whatever it selects following the copy as whatever is left is then placed in "Other" worksheet for analysis. I have posed the question, did you say this has been answered?

Hi apologies,

Just noticed your reply, I will try this now thank you so much.

Fletch

 

[Peter_SSs]

I just put that figure down to cover but I'm sure there is a neater way for this.

That didn't really answer the question ..

about how many rows in the original sheet?