Combine results of two Autofilters

[vanwooten]

I want to be able to combine the results of the following autofilters that work perfectly individually:

ActiveSheet.ListObjects("Trades").Range.AutoFilter Field:=20, Criteria1:="=*-*" & Format(Month(Date), "00")
ActiveSheet.ListObjects("Trades").Range.AutoFilter Field:=14, Criteria1:="="

So I would get records from the current month on the first filter on column 20 PLUS records where column 14 is blank.

 

[Domenic]

You can simply run those two lines of code one after another. Although, it can be re-written as follows...

With ActiveSheet.ListObjects("Trades").Range
    .AutoFilter Field:=20, Criteria1:="=*-*" & Format(Month(Date), "00")
    .AutoFilter Field:=14, Criteria1:="="
End With

Hope this helps!

 

[vanwooten]

You can simply run those two lines of code one after another. Although, it can be re-written as follows...

With ActiveSheet.ListObjects("Trades").Range
    .AutoFilter Field:=20, Criteria1:="=*-*" & Format(Month(Date), "00")
    .AutoFilter Field:=14, Criteria1:="="
End With

Hope this helps!

That code runs like an AND. I need an OR.

 

[Peter_SSs]

I need an OR.

Then you could either add another column to the ListObject to mark rows that meet either criteria and then AutoFilter or you could use Advanced Filter.

Here is an Advanced Filter option which assumes nothing in the second column to the right of the ListObject

Sub Test()
  Dim rCrit As Range
  
  With ActiveSheet.ListObjects("Trades").Range
    Set rCrit = .Offset(, .Columns.Count + 1).Resize(2, 1)
    rCrit.Cells(2).Formula = Replace(Replace("=OR(ISNUMBER(SEARCH(""-*""&TEXT(TODAY(),""mm""),#)),^="""")", "#", .Cells(2, 20).Address(0, 0)), "^", .Cells(2, 14).Address(0, 0))
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    rCrit.Cells(2).ClearContents
  End With
End Sub