CODEWITHSUNDEEP
excelvbafilter
Chopin
Chopin

Reputation: 214

Macro Filter Column by two different values - Excel

There's a few questions on this topic. I've tried a few ways and can't seem to get it working.

I've got an auto-copy script working that takes specific values from one sheet and copies them to another sheet. This works fine if I copy on one value but I cant get it to work when adding a second value. So copy value1 or value2. Below is my code

Sub FilterAndCopy()
    Dim rng As Range, sht1 As Worksheet, sht2 As Worksheet

    Set sht1 = Worksheets("LOG")
    Set sht2 = Worksheets("P Or A")

    sht2.UsedRange.ClearContents

    With Intersect(sht1.Columns("B:BP"), sht1.UsedRange)
        .Cells.EntireColumn.Hidden = False ' unhide columns
        If .Parent.AutoFilterMode Then .Parent.AutoFilterMode = False
        .AutoFilter field:=1, Criteria1:="P"
        .AutoFilter field:=1, Criteria1:="A"

Bassed off other questions, I have tried to add the following:

1) Operator:=xlFilterValues,
2) Operator:=xlOr,

But it doesn't work. Is something else hindering the script?

Upvotes: 0

Views: 275

Answers (1)

alowflyingpig
alowflyingpig

Reputation: 738

From my knowledge, you can only use the filter option for up to 2 criteria. Anything outside of that you need arrays..

To do this, I assume your headers are in the range B:BP on row 1. and the field you want to filter on is col B

sht1.Range("B1:BP1").AutoFilter Field:=1, Criteria1:="P", Operator:=xlOr, Criteria2:="A"

Give that a go.. Change the range/headers where aplicable and also the field number has to change for which col you are basing the filter on.

Upvotes: 1

Related Questions