How to Combine Two Field in VBA Filter Function

[gnaga]

Hi,

Rng1.AutoFilter field:=10
                    Rng1.AutoFilter field:=18, Criteria1:="=" & ""
                    Rng1.AutoFilter field:=2, Criteria1:=">=" & ACel.Offset(I, -1), Operator:=xlAnd, Criteria2:="<=" & ACel.Offset(I, -1) + 1 
                    Rng1.AutoFilter field:=8, Criteria1:="=" & unqSmpIDs(J)

In the above code I want to add one more additonal sub criteria to Criteria2 with AND condition

for example

Rng1.AutoFilter field:=2, Criteria1:=">=" & ACel.Offset(I, -1), Operator:=xlAnd, Criteria2:=("<=" & ACel.Offset(I, -1) + 1 And ACel.Offset(I, 2) >= 0)

What is the syntax for this coding?

 

[Alex Blakenburg]

You can't do more than 2 Custom Criteria on a single field. You will need to either use a helper column or switch to using an advanced filter.

 

[gnaga]

Thanks Alex. Could you please guide me how to use Advanced Filter Function in VBA for my requirement?

 

[Alex Blakenburg]

Can you give:
• some sample data using XL2BB
• The name of the sheet
• what is in the criteria fields

 

[Alex Blakenburg]

By the way can you explain your additional criteria.
Your initial criteria is that Field 2 is between to values (Value and Value + 1). One would assume that both these values are > 0, so what it the criteria for Field 2 that the 3rd item is meant to add ?

 

[gnaga]

Thanks Alex.

I would like to filter two dates (ex. 1st and 2nd may) this is my intial criteria. Additional criteria not from field 2 but from field 3 which is time data for 2nd May must be less than 6AM.

Basically I would like to compare two column in single criteria.

Sample data and expected out put is given below.

Book1
	C	D	E	F	G	H	I	J	K
3							Filter Output
4	Rec No	Date	Time	ID		Rec No	Date	Time	ID
5	1	01-May-22	4:00:00 AM	17S0046		1	01-May-22	4:00:00 AM	17S0046
6	2	01-May-22	4:00:00 AM	11S0046		2	01-May-22	4:00:00 AM	11S0046
7	3	01-May-22	4:00:00 AM	13S0010		3	01-May-22	4:00:00 AM	13S0010
8	4	02-May-22	4:00:00 AM	12S03BS		4	02-May-22	4:00:00 AM	12S03BS
9	5	01-May-22	4:00:00 AM	18S03AS
10	6	02-May-22	7:00:00 AM	17S0010
11	7	02-May-22	8:00:00 AM	15S0008
12	8	03-May-22	4:00:00 AM	14S0008
13	9	03-May-22	4:00:00 AM	19S0008
14	10	04-May-22	4:00:00 AM	17S0011
15	11	04-May-22	4:00:00 AM	11S0010
16	12	04-May-22	8:00:00 AM	12S0002
17	13	05-May-22	8:00:00 AM	12S0002
18	14	06-May-22	8:00:00 AM	12S0002
Sheet1

 

[Alex Blakenburg]

It is quite late here so I am about to log off. Will have a look at it tomorrow.
So just to be clear. Any time on 1 May but only up to 6AM on 2 May ?

Can the code assume the heading at H4:K4 will already be there ?

 

[gnaga]

Yes. You are right.

1 may can be of any time but 2nd may up to 6AM.

I can maaintain the header H4:K4.

 

[Peter_SSs]

Why isn't Rec No 5 included in the results since its date is 1 May?

Also, can you confirm whether you want to actually filter the original data in place, or whether you want the relevant data extracted to a separate location like in your post #6 Mini Sheet?

What is the name of the table that the original data is in?

I am assuming ..

1. That in your sample Rec No 5 should be included in the results since its date is 1 May
2. That you want the data filtered in place, not the results transferred to another place.
3. That the table is called "Table1" and is on the Active Sheet
4. That the table consists of the four columns shown only
5. That, like your samples, Rec Nos are not repeated in that column.

If all those assumptions are correct, then you can use AutoFilter without a helper column as follows.

Sub Filter_Special()
  Dim a As Variant, CritAry As Variant
  Dim i As Long, k As Long
 
  a = Range("Table1").Value
  ReDim CritAry(1 To UBound(a))
  For i = 1 To UBound(a)
    If a(i, 2) = DateSerial(2022, 5, 1) Or (a(i, 2) = DateSerial(2022, 5, 2) And a(i, 3) < TimeSerial(6, 0, 0)) Then
      k = k + 1
      CritAry(k) = a(i, 1)
    End If
  Next i
  ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=Split(Join(CritAry)), Operator:=xlFilterValues
End Sub

 

[Alex Blakenburg]

@Peter_SSs, I don't know which is faster but I have found that this also works instead of the Split-Join approach.

  CritAry = Application.Trim(CritAry)
  ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=CritAry, Operator:=xlFilterValues