RE: Need Macro Help AutoFiltering for 2 Different Date Ranges Within Same Column

[justinbelkin]

Good news is that I can get this code to work when I search for just 1 Date Range (i.e. 01/01/2013-03/31/2013 = "Q1"). I've also gotten it to work by adding additional filter criteria for other columns/"Fields". However, I can't get the code below to work. I've tried several variations, as well.

What I want to do is as follows: Assuming the IF Conditional search criteria are satisfied at the top of the code (variables defined elsewhere in the module), then I want the Table "PipelineTable" on Sheet!Pipeline to AutoFilter on 1 column (i.e. Field 24), but to filter on 2 ranges of dates, both Q1 2013 (01/01/2013-03/31/2013) and Q1 2014 (01/01/2014-03/31/2014). Right now, my code won't process this request (it results in a 1004 object defined error).

I'm not sure if this is a limitation of Excel's AutoFilter function, or whether my syntax is wrong (I'm guessing/hoping the latter). It may require an AutoFilter Array, however, I'm not sure what syntax to use to have the array capture those 2 date ranges (i.e. Q1 2013 and Q1 2014) when the dates in the table that will comprise the array are dynamically changing as users make edits and add new dates to the table.

    If SearchPeriod = "Q1" _
        And SearchYear = "All" _
        And SearchCategory = "All" _
        And SearchProductLine = "All" Then
        
    Sheets("Pipeline").ListObjects("PipelineTable").Range.AutoFilter        'Turns off any Auto Filters
      
    ThisWorkbook.Sheets("Summary").Range("A6", Range("X" & Rows.Count).End(xlDown)).EntireRow.ClearContents     'Deletes contents from previous search

        With Sheets("Pipeline")
            .AutoFilterMode = False     'Auto Filters for all Q1 2013 and 2014 (01/01/2013-03/31/2013) records
            Sheets("Pipeline").ListObjects("PipelineTable").Range.AutoFilter Field:=24, _
                Operator:=xlFilterValues, Criteria1:=">=01/01/2013", _
                Operator:=xlAnd, Criteria2:="<=03/31/2013", _
                Operator:=xlAnd, Criteria3:=">=01/01/2014", _
                Operator:=xlAnd, Criteria4:="<=03/31/2014"
      
    ThisWorkbook.Sheets("Pipeline").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
        Worksheets("Summary").Range("A6").PasteSpecial xlPasteValues      'This copies-and-pastes the search results from the "Pipeline" to "Summary" tabs

        End With

    End If

    ThisWorkbook.Sheets("Summary").Range("A6").Select

[6StringJazzer]

The problem with your criteria is that you are specifying AND, and the conditions are mutually exclusive. A date can't be both earlier than 3/31/2013 and greater than 1/1/2014. (I'm not sure what happens if you mix AND and OR in the filter criteria.)

Try this change. AutoFilter does not have built-in filtering by quarter, although it does by month. You can specify each month to be included.

    If SearchPeriod = "Q1" _
        And SearchYear = "All" _
        And SearchCategory = "All" _
        And SearchProductLine = "All" Then
        
    Sheets("Pipeline").ListObjects("PipelineTable").Range.AutoFilter        'Turns off any Auto Filters
      
    ThisWorkbook.Sheets("Summary").Range("A6", Range("X" & Rows.Count).End(xlDown)).EntireRow.ClearContents     'Deletes contents from previous search

        With Sheets("Pipeline")
            .AutoFilterMode = False     'Auto Filters for all Q1 2013 and 2014 (01/01/2013-03/31/2013) records
            Sheets("Pipeline").ListObjects("PipelineTable").Range.AutoFilter Field:=24, _
                Operator:= xlFilterValues, Criteria2:=Array(1, "1/28/2013", 1, "2/28/2013", 1, "3/17/2013", 1, "1/28/2014", 1, "2/28/2014", 1, "3/17/2014")

    ActiveSheet.Range("$A$1:$A$91").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(1, "1/28/2013", 1, "2/28/2013", 1, "3/17/2013", 1, _
        "10/29/2013", 1, "11/29/2013", 1, "12/26/2013")

      
    ThisWorkbook.Sheets("Pipeline").UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
        Worksheets("Summary").Range("A6").PasteSpecial xlPasteValues      'This copies-and-pastes the search results from the "Pipeline" to "Summary" tabs

        End With

    End If

    ThisWorkbook.Sheets("Summary").Range("A6").Select

[justinbelkin]

Thanks, Jeff. I was just about to my post my own solution, which happens to be the same as yours, so that's comforting. I wish there was a more elegant way to do this, but I still think going the AutoFilter built-in Excel function route is quicker and more efficient than having to write a script with a loop to achieve the same result.

[6StringJazzer]

By the way, AutoFilter can only take two arguments Criteria1 and Criteria2, and you used 4. That is probably why you got the error.