PDA

View Full Version : Autofilter 1 column based on multiple criteria



aloy78
10-26-2011, 08:36 PM
Hi all,

I'm trying to get this code to work but some how it just doesn't connnect. I want to filter off records that consists of 3 criterias as follows:

1. Date Value of this month(xlFilterThisMonth)
2. and the next (xlFilterNextMonth)
3. text value which is equal to "tba"




' Code pasted in the sheet itself
If Target.Address = "$E$8" Then
Call Filter_Current_Month()
End If

' Code pasted in the Module
Sub Filter_Current_Month()
Dim awb As Workbook
Dim wsIAir As Worksheet

Set awb = ThisWorkbook
Set wsIAir = Worksheets("Imp-Air")

With wsIAir
.Range("A:ZZ").EntireColumn.Hidden = False ' show all hidden column from master sheet
.AutoFilterMode = False
.Range("B18:BE18").AutoFilter
.Range("B18:BE18").AutoFilter Field:=20, Criteria1:=xlFilterThisMonth, _
Operator:=xlAnd, Criteria2:="tba", _
Operator:=xlAnd, Criteria3:=xlFilterNextMonth
End With
Application.Calculate
End Sub

GTO
10-26-2011, 10:36 PM
Greetings aloy,

I am guessing a bit, but did you maybe record this initially and edit from there? I am currently in Excel 2010, and there is still a 2-criteria max.

.Range("B18:BE18").AutoFilter Field:=20, Criteria1:=xlFilterThisMonth, _
Operator:=xlAnd, Criteria2:="tba", _
Operator:=xlAnd, Criteria3:=xlFilterNextMonth

Also, you need a Field argument for each Criteria.

Does that help?

Mark

aloy78
10-27-2011, 07:48 PM
Hi Mark,

Nah. I just got it from some site and did some changes. By the way I didn't know that there is a 2-criteria max. So basically what arguments did I left out assuming if i'm just using criteria 1 & 2.

I had no problems with my other filters, just these.