Auto Filter Exclude items

[exclusiveicon]

Hi,

I have this code and i'm getting an error is there a way to achieve this?

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:= _
Array("<>313586885", "<>313586877", "<>313586844", "<>313586752", _
"<>313400772", "<>313400855", "<>313400749", "<>313400731", "<>313400673", _
"<>313400657"), Operator:=xlFilterValues

There are 1000 different items in Field 3.

Thanks

[Domenic]

Try using AdvancedFilter instead...

Please Login or Register  to view this content.

To maintain the table's formatting, try replacing...

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Hope this helps!

[exclusiveicon]

Hi Domenic,

Maybe i'm suppose to change some other parts to your code but all it is doing it copy and pasting it to another page...

to Clarify. I have these headers, which are located on sheet "Step1"
Sector Portfolio Cusip Amount OFV

I have code currently that filters the sector column how I want.
Then i need the cusip column filtered to remove those sets of cusips i provided above.

Then I have a loop that open 10 workbooks

as part of the loop i want to add that each wookbook opened applies a different filter to the portfolio column and then copys column cusip, amount and ofv to sheet "Step2" as paste values. then a few more steps and then ultimatly copy over to the workbook that was opened and saved. then Next loop.





Set CopyToRng = Worksheets("Info2").Range("A1") 'change the destination range accordingly

With ActiveSheet.ListObjects("Table1").Range
LastCol = .Columns.Count + .Columns(1).Column - 1
With .Parent
.Cells(2, LastCol + 2).FormulaR1C1 = "=ISNA(MATCH(RC" & .Columns(3).Column & ",{313586885,313586877," & _
"313586844,313586752,313400772,313400855,313400749,313400731,313400673,313400657},0))"
Set CritRng = .Cells(1, LastCol + 2).Resize(2)
End With
.AdvancedFilter Action:=xlFilterInPlace, criteriarange:=CritRng, copyToRange:=", unique:=False"
.Copy CopyToRng
.Parent.ShowAllData
End With

[Domenic]

In future, can you please use code tags when posting code. It makes it easier to read. In any case, I see that some how a line of code got messed up when I posted it. It should be...

Please Login or Register  to view this content.

Does this help?

[exclusiveicon]

Page.png
this is the basic screen i'm trying to filter. Two filters at first. applied to column 1 and 3 and then a third filter applied in a loop to column 2

Please Login or Register  to view this content.

I'm getting an error at .Parent.ShowAllData

[Domenic]

Can you please be specific? What error message and number are you getting?