Macro Filter Column by two different values

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?

edited Nov 23 '18 at 0:38

asked Nov 22 '18 at 23:57

JPA0888

I'd suggest using the macro recorder to record the steps you take as you perform the task manually, and then you can edit the code that Excel generates to fine tune it as necessary. See: Recording a Macro to Generate Code and Revising Recorded Visual Basic Macros.

– ashleedawg
Nov 23 '18 at 0:16

Thanks @ashleedawg. I'll keep that in mind

– JPA0888
Nov 23 '18 at 0:38

add a comment |

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

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?

edited Nov 23 '18 at 0:38

asked Nov 22 '18 at 23:57

JPA0888

I'd suggest using the macro recorder to record the steps you take as you perform the task manually, and then you can edit the code that Excel generates to fine tune it as necessary. See: Recording a Macro to Generate Code and Revising Recorded Visual Basic Macros.

– ashleedawg
Nov 23 '18 at 0:16

Thanks @ashleedawg. I'll keep that in mind

– JPA0888
Nov 23 '18 at 0:38

add a comment |

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

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?

edited Nov 23 '18 at 0:38

asked Nov 22 '18 at 23:57

JPA0888

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

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?

excel vba filter

edited Nov 23 '18 at 0:38

asked Nov 22 '18 at 23:57

JPA0888

edited Nov 23 '18 at 0:38

asked Nov 22 '18 at 23:57

JPA0888

edited Nov 23 '18 at 0:38

asked Nov 22 '18 at 23:57

JPA0888

asked Nov 22 '18 at 23:57

JPA0888

asked Nov 22 '18 at 23:57

JPA0888

I'd suggest using the macro recorder to record the steps you take as you perform the task manually, and then you can edit the code that Excel generates to fine tune it as necessary. See: Recording a Macro to Generate Code and Revising Recorded Visual Basic Macros.

– ashleedawg
Nov 23 '18 at 0:16

Thanks @ashleedawg. I'll keep that in mind

– JPA0888
Nov 23 '18 at 0:38

add a comment |

I'd suggest using the macro recorder to record the steps you take as you perform the task manually, and then you can edit the code that Excel generates to fine tune it as necessary. See: Recording a Macro to Generate Code and Revising Recorded Visual Basic Macros.

– ashleedawg
Nov 23 '18 at 0:16

Thanks @ashleedawg. I'll keep that in mind

– JPA0888
Nov 23 '18 at 0:38

I'd suggest using the macro recorder to record the steps you take as you perform the task manually, and then you can edit the code that Excel generates to fine tune it as necessary. See: Recording a Macro to Generate Code and Revising Recorded Visual Basic Macros.

– ashleedawg
Nov 23 '18 at 0:16

Thanks @ashleedawg. I'll keep that in mind

– JPA0888
Nov 23 '18 at 0:38

add a comment |

1 Answer
1

active

oldest

votes

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.

edited Nov 23 '18 at 1:11

answered Nov 23 '18 at 0:53

alowflyingpig

1519

Thanks @alowflyingpig. I'll give that a go. It should be Or though. So A or P

– JPA0888
Nov 23 '18 at 1:01

Field:=1 for B column in Range("B1:BP1").

– VBasic2008
Nov 23 '18 at 1:10

buggr, missed that sorry.. updated answer with field and or. Field always start from the first col in range.

– alowflyingpig
Nov 23 '18 at 1:11

So the values are just in Column B sorry. I athen copy all rows from Column B:BP when values in Col B = A or P

– JPA0888
Nov 23 '18 at 1:16

No dice mate. It just creates filters in the top row of sheet 1

– JPA0888
Nov 23 '18 at 1:36

|
show 1 more comment

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53439229%2fmacro-filter-column-by-two-different-values-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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.

edited Nov 23 '18 at 1:11

answered Nov 23 '18 at 0:53

alowflyingpig

1519

Thanks @alowflyingpig. I'll give that a go. It should be Or though. So A or P

– JPA0888
Nov 23 '18 at 1:01

Field:=1 for B column in Range("B1:BP1").

– VBasic2008
Nov 23 '18 at 1:10

buggr, missed that sorry.. updated answer with field and or. Field always start from the first col in range.

– alowflyingpig
Nov 23 '18 at 1:11

So the values are just in Column B sorry. I athen copy all rows from Column B:BP when values in Col B = A or P

– JPA0888
Nov 23 '18 at 1:16

No dice mate. It just creates filters in the top row of sheet 1

– JPA0888
Nov 23 '18 at 1:36

|
show 1 more comment

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.

edited Nov 23 '18 at 1:11

answered Nov 23 '18 at 0:53

alowflyingpig

1519

Thanks @alowflyingpig. I'll give that a go. It should be Or though. So A or P

– JPA0888
Nov 23 '18 at 1:01

Field:=1 for B column in Range("B1:BP1").

– VBasic2008
Nov 23 '18 at 1:10

buggr, missed that sorry.. updated answer with field and or. Field always start from the first col in range.

– alowflyingpig
Nov 23 '18 at 1:11

So the values are just in Column B sorry. I athen copy all rows from Column B:BP when values in Col B = A or P

– JPA0888
Nov 23 '18 at 1:16

No dice mate. It just creates filters in the top row of sheet 1

– JPA0888
Nov 23 '18 at 1:36

|
show 1 more comment

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.

edited Nov 23 '18 at 1:11

answered Nov 23 '18 at 0:53

alowflyingpig

1519

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.

edited Nov 23 '18 at 1:11

answered Nov 23 '18 at 0:53

alowflyingpig

1519

edited Nov 23 '18 at 1:11

answered Nov 23 '18 at 0:53

alowflyingpig

1519

answered Nov 23 '18 at 0:53

alowflyingpig

1519

answered Nov 23 '18 at 0:53

alowflyingpig

1519

Thanks @alowflyingpig. I'll give that a go. It should be Or though. So A or P

– JPA0888
Nov 23 '18 at 1:01

Field:=1 for B column in Range("B1:BP1").

– VBasic2008
Nov 23 '18 at 1:10

buggr, missed that sorry.. updated answer with field and or. Field always start from the first col in range.

– alowflyingpig
Nov 23 '18 at 1:11

So the values are just in Column B sorry. I athen copy all rows from Column B:BP when values in Col B = A or P

– JPA0888
Nov 23 '18 at 1:16

No dice mate. It just creates filters in the top row of sheet 1

– JPA0888
Nov 23 '18 at 1:36

|
show 1 more comment

Thanks @alowflyingpig. I'll give that a go. It should be Or though. So A or P

– JPA0888
Nov 23 '18 at 1:01

Field:=1 for B column in Range("B1:BP1").

– VBasic2008
Nov 23 '18 at 1:10

buggr, missed that sorry.. updated answer with field and or. Field always start from the first col in range.

– alowflyingpig
Nov 23 '18 at 1:11

So the values are just in Column B sorry. I athen copy all rows from Column B:BP when values in Col B = A or P

– JPA0888
Nov 23 '18 at 1:16

No dice mate. It just creates filters in the top row of sheet 1

– JPA0888
Nov 23 '18 at 1:36

Thanks @alowflyingpig. I'll give that a go. It should be Or though. So A or P

– JPA0888
Nov 23 '18 at 1:01

Field:=1 for B column in Range("B1:BP1").

– VBasic2008
Nov 23 '18 at 1:10

buggr, missed that sorry.. updated answer with field and or. Field always start from the first col in range.

– alowflyingpig
Nov 23 '18 at 1:11

So the values are just in Column B sorry. I athen copy all rows from Column B:BP when values in Col B = A or P

– JPA0888
Nov 23 '18 at 1:16

No dice mate. It just creates filters in the top row of sheet 1

– JPA0888
Nov 23 '18 at 1:36

|
show 1 more comment

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk