CODEWITHSUNDEEP
vbaexcel
TarunS
TarunS

Reputation: 59

Filtering for multiple criteria in different columns including string and date in VBA

I have two columns namely "Status" and "Finish Date". I want to filter for "OPEN" and "CLOSED" in Status column first and then filter for "1/0/1900" in Finish Date column.

For this I wrote the following code:

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet

        .AutoFilterMode = False
        .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:=Array( _
            "OPEN", "CLOSED"), Operator:=xlFilterValues
        .Range("A1:A" & LR).AutoFilter Field:=2, Criteria1:=Array(0, "1/0/1900"), Operator:= _
            xlFilterValues

End With

But this did not work. At first I get the Error (AutoFilter Method of Range Class Failed 1004). Then by mistake I removed the 1st line in the With statement(.AutoFilterMode = False) and I did not get any error. However the filter returned no results at all.

I thought that there is something wrong with my date filter since the Status column filter was working earlier. So I researched and wrote the code below for the date filter alone but again it does not work.

Dim dDate As Date
Dim strDate As String
Dim lDate As Long, LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row

    dDate = DateSerial(1900, 1, 0)
    lDate = dDate

    ActiveSheet.Range("A1:A" & LR).AutoFilter Field:=2, Criteria1:=">=" & lDate, _
                     Operator:=xlAnd, Criteria2:="<" & lDate + 1

Please help in getting these two filters to work.

Thanks in advance.

Upvotes: 1

Views: 15066

Answers (4)

rajesh
rajesh

Reputation: 11

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Integer
    
    If Target.Address = "$B$2" Then
    If Range("B2") = "All" Then
    
      Range("A5").AutoFilter
      Range("A5").AutoFilterMode = False
      Else
        For i = 2 To Worksheets.Count
            Worksheets(i).Range("A5").AutoFilter Field:=2, Criteria1:=Target.Value _
    Operator:=xlOr, Criteria2:="=OPEN"
    Worksheets(i).Range("A5")..AutoFilter Field:=2, Criteria2:=Range("B2")
            
        Next
        End If
    End If
    
End Sub

Upvotes: 1

user3598756
user3598756

Reputation: 29421

you mess things up a little bit

try this

Sub Test()
    With ActiveSheet  '<--| reference your sheet
        .AutoFilterMode = False '<--| remove any existing filtering
        With Range("B1", .Cells(.Rows.count, 1).End(xlUp)) '<--| reference its range in columns A:B from row 1 down to column A last not empty row
            .AutoFilter Field:=1, Criteria1:=Array("OPEN", "CLOSED"), Operator:=xlFilterValues '<--| filter on referenced range 1st column with "OPEN" or "CLOSED" values
            .AutoFilter Field:=2, Criteria1:="1/0/1900" '<--| filter on referenced range 2nd column with "1/0/1900" value
        End With
    End With
End Sub

BTW you'd better substitute ActiveSheet with some Worksheets("mySheetName") (where you change "mySheetName" to your actual sheet name) since relying on ActiveSheet can be misleading

Upvotes: 3

Swapnil
Swapnil

Reputation: 26

I Consider your data is like as below

column A    Column B
Status  Finish Date
OPEN    1/0/1900
CLOSED  1/0/1900
OPEN    1/0/1900
CLOSED  1/0/1900
OPEN    1/0/1904
CLOSED  1/0/1905
OPEN    1/0/1906
CLOSED  1/0/1907
OPEN    1/0/1908
CLOSED  1/0/1909
OPEN    1/0/1910
CLOSED  1/0/1911
OPEN    1/0/1900
CLOSED  1/0/1900
OPEN    1/0/1900
CLOSED  1/0/1900
OPEN    1/0/1900
CLOSED  1/0/1900
OPEN    1/0/1905
OPEN    1/0/1906
CLOSED  1/0/1906
OPEN    1/0/1906
CLOSED  1/0/1906
OPEN    1/0/1906
CLOSED  1/0/1906
OPEN    1/0/1906
CLOSED  1/0/1906
OPEN    1/0/1906
x   1/0/1906
y   1/0/1906
Z   1/0/1906
A   1/0/1906
b   1/0/1906

You can Type the formula in column C (at C2 Cell)

=IF(AND((OR(A2="open",A2="Closed")),B2= "1/0/1900"),"Yes","No")

Drag this formula till last row(last of column A or B)

It will gives you output like below:

Status  Finish Date Result
OPEN    1/0/1900    Yes
CLOSED  1/0/1900    Yes
OPEN    1/0/1900    Yes
CLOSED  1/0/1900    Yes
OPEN    1/0/1904    No
CLOSED  1/0/1905    No
OPEN    1/0/1906    No
CLOSED  1/0/1907    No
OPEN    1/0/1908    No
CLOSED  1/0/1909    No
OPEN    1/0/1910    No
CLOSED  1/0/1911    No
OPEN    1/0/1900    Yes
CLOSED  1/0/1900    Yes
OPEN    1/0/1900    Yes
CLOSED  1/0/1900    Yes
OPEN    1/0/1900    Yes
CLOSED  1/0/1900    Yes
OPEN    1/0/1905    No
OPEN    1/0/1906    No
CLOSED  1/0/1906    No
OPEN    1/0/1906    No
CLOSED  1/0/1906    No
OPEN    1/0/1906    No
CLOSED  1/0/1906    No
OPEN    1/0/1906    No
CLOSED  1/0/1906    No
OPEN    1/0/1906    No
x   1/0/1906    No
y   1/0/1906    No
Z   1/0/1906    No
A   1/0/1906    No
b   1/0/1906    No

then you can easily set the filter on column c as criteria "Yes" or if you want to copy the data , set the filter on "yes"

and use the code to copy data

Range("A2:C1048576").specialcells(xlcellTypeVisible).copy Destination:=Range("D2")

Upvotes: 0

user3391546
user3391546

Reputation: 30

TarunS, looks like you are selecting only one column ( Column A ) in the Range() but trying to apply filter in two columns. I have created excel worksheet where column A is "Status" and column B is "Finish Date" and I am able to create filter as you explained with following code:-

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet

    .AutoFilterMode = False
    .Range("A1:B" & LR).AutoFilter Field:=1, Criteria1:="=CLOSED", _
    Operator:=xlOr, Criteria2:="=OPEN"
    .Range("A1:B" & LR).AutoFilter Field:=2, Criteria1:="1/0/1900"

 End With

Please note that I have equal number of items in Column A and Column B and I am using Excel 2010. Let me know if you still face any problem with the code.

Upvotes: 0

Related Questions