How to filter multiple columns with condition using excel VBA

Question

Scenario

I have the following excel sheet with values

What I need

I want to filter out values only when the following conditions met

If Status = "RO" OR Status = "SS" OR (Status = "NO" AND StartDate <>"") OR Status = "INVEST")

using excel filter with VBA.

This is a very big table and if it is possible through filtering techniques, it will be faster rather than doing comparison of values one by one and copy one by one. That is why I want to use filter. Is this possible?

A normal recording giving only the following which I can't figure out how to do in my situation

Eg: ActiveSheet.Range("$A$3:$D$21").AutoFilter Field:=4, Criteria1:="SS"

But how to check with filter if blank on start date and a particular value on Status?

QHarr · Accepted Answer · 2018-09-01 09:20:20Z

1

Set your data up as an Excel table by selecting a cell in the populated range and pressing Ctrl+T.

Add an additional column at the end called Include. In the top cell of that column put the following (it will autofilter down all rows):

=OR([Status] = "RO", [Status]  = "SS", AND([Status] = "NO",[Start Date] <>""),[Status] = "INVEST")

The use the following VBA lines in your code:

With ThisWorkbook.Worksheets("Yoursheetname").ListObjects("YourTableName").Range
    .AutoFilter
    .AutoFilter Field:=5, Criteria1:= _
    "TRUE"
End With

Data:

answered Sep 1, 2018 at 9:20

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to filter multiple columns with condition using excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related