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?

 

[Peter_SSs]

@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

Yes, I was using a different array structure earlier and when I changed that, I forgot to simply that line. Thanks for posting that simplification.

that the Rec No column does not contain duplicates.

 

[gnaga]

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

Thanks a lot @Peter_SSs your solution works fine for my requirement.

I have no knowledge on how the split function is splitting the Rec No exactly as its original value.
For Ex Rec 10 and Rec 11 will become 1011 after joining. How come this get splitting as 10 & 11 why not as 1,0,1,1?

 

[gnaga]

Yes, I was using a different array structure earlier and when I changed that, I forgot to simply that line. Thanks for posting that simplification.

that the Rec No column does not contain duplicates.

Thanks a lot Alex, I tired your suggestion as well and it works fine.

I have the same doubt how the filter function is splitting the RecNo as its original value even after joining with Trim Function.

After executing Trim function, value of CritAry for RecNo 10,11,12,13 become 10111213 but filter function takes it as 10,11,12,13 How?

 

[Peter_SSs]

For Ex Rec 10 and Rec 11 will become 1011 after joining.

No, that is not correct.
After the Join function is applied to 10 and 11 the result is "10 11" because the default delimiter for Join is a space character.
Similarly, the default delimiter for the Split function is a space so that again returns "10" and "11"

However, as Alex pointed out in post #10 there should be no need to do that Join/Split anyway.

In my code, try this replacement

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

 

[Alex Blakenburg]

If understand you correctly both options work for you but you want to know how it works.

The first thing you need to know is that Peter is relying on your "Rec No" being a unique index number for each row.
If you manually record a macro that selects rows using the Rec No you would get something that looks like this.

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues

This thing to note here is thar Rec No is a Number but the Criteria needs the numbers to be treated as Strings (Text).
The initial loop loads the record no Column 1 of the array a into CritAry, and it retains it data type as a number

CritAry(k) = a(i, 1)

Output looks like: Array(1, 2, 3, 4, 5)

Since we need that as a string, one way it to convert it is:
where CritAry is Array(1, 2, 3, 4, 5)
Join(Array(1, 2, 3, 4, 5)) ---> interim result ---> as string value of "1 2 3 4 5" (default delmiter if unspecified is a space)
Split("1 2 3 4 5") ---> final result ---> Array("1", "2", "3", "4", "5") (default delmiter if unspecified is a space)

Another way was:
Application.Trim(Array(1, 2, 3, 4, 5)) ---> final result ---> Array("1", "2", "3", "4", "5")

On revisiting this, we probably could have done away with all of that and converted it to a String on a line by line basis when we loaded it to the array.

CritAry(k) = CStr(a(i, 1))

 

[gnaga]

No, that is not correct.
After the Join function is applied to 10 and 11 the result is "10 11" because the default delimiter for Join is a space character.
Similarly, the default delimiter for the Split function is a space so that again returns "10" and "11"

However, as Alex pointed out in post #10 there should be no need to do that Join/Split anyway.

In my code, try this replacement

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

Thank you very much @Peter_SSs for your detailed explanation. Now I know that the Join and Split function use space as default delimiter. Thanks

 

[gnaga]

If understand you correctly both options work for you but you want to know how it works.

The first thing you need to know is that Peter is relying on your "Rec No" being a unique index number for each row.
If you manually record a macro that selects rows using the Rec No you would get something that looks like this.

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= Array("1", "2", "3", "4", "5"), Operator:=xlFilterValues

This thing to note here is thar Rec No is a Number but the Criteria needs the numbers to be treated as Strings (Text).
The initial loop loads the record no Column 1 of the array a into CritAry, and it retains it data type as a number

CritAry(k) = a(i, 1)

Output looks like: Array(1, 2, 3, 4, 5)

Since we need that as a string, one way it to convert it is:
where CritAry is Array(1, 2, 3, 4, 5)
Join(Array(1, 2, 3, 4, 5)) ---> interim result ---> as string value of "1 2 3 4 5" (default delmiter if unspecified is a space)
Split("1 2 3 4 5") ---> final result ---> Array("1", "2", "3", "4", "5") (default delmiter if unspecified is a space)

Another way was:
Application.Trim(Array(1, 2, 3, 4, 5)) ---> final result ---> Array("1", "2", "3", "4", "5")

On revisiting this, we probably could have done away with all of that and converted it to a String on a line by line basis when we loaded it to the array.

CritAry(k) = CStr(a(i, 1))

Thanks a lot Alex. Thanks for your time and explanation of my clarification. I understand from your post that we can use Trim function not only to remove space but also to convert numerical elements of an array to string. Thanks

 

[Alex Blakenburg]

I appreciate the feedback. Thank you.
PS: It is also useful in VBA in that it lets you apply it to a whole array without having to loop through the array.

 

[Peter_SSs]

Thank you very much @Peter_SSs for your detailed explanation.

You're very welcome. Cheers.