Using Autofilter in VBA with a range of criteria


  • I have working code to autofilter the list I'm interested in for a single criterion in several fields.  However, I can't find anything anywhere that shows how to write the object model fields so that I can use a date range.


    ie. I thought that I would be able to use

    RangeObject.AutoFilter(Field:=1, Criteria1 > Date1, Operator:=xlAnd, Criteria2 < Date2)

    I'm not sure that it's possible, but maybe someone knows a workaround.

    Wednesday, January 31, 2007 7:30 PM

All replies

  • I just thought of something that I should add to clarify my request for help here.

    I'll give the snippet of code to help:


        week = InputBox("Enter the desired Week Number.", "Date Query")

        Selection.AutoFilter 8, "REBALE"
        Selection.AutoFilter Field:=9, Criteria1:="28"
        Selection.AutoFilter 13, week

    This currently works for sorting my data by the Week Number column.  However, I want to have two seperate input boxes that give me the capability to use 2 different string criteria while using > or < operators.  I know that you could simply put the week numbers in the following:

    .AutoFilter (Field, Criteria1:=">=3", xlAnd, Criteria2:="<=6")

    but I want it to be interactive, if possible.


    I hope that's a bit more clear than mud!



    Wednesday, January 31, 2007 8:13 PM
  • Well, I believe that I finally found an answer by trying to concatenate strings to make the thing work.

    It might not be the slickest way, but I thought I would post it since it works and someone else could have a similar question.


        startweek = InputBox("Enter the desired Week Number.", "Start Week")
        week1 = ">=" & startweek
        endweek = InputBox("Enter the desired Week Number.", "End Week")
        week2 = "<=" & endweek
    ' User to input the needed defect codes
        dcode = InputBox("Enter the code in question.", "Defect Code")
    ' User to input the status
        status = InputBox("Enter the status in question. (Note that it must be either REBALE, REJECT, or HELD.)", "Bale Status")

        Workbooks("Production Database Master.xls").Activate
        Application.ScreenUpdating = False
        row_count = Selection.Rows.Count - 1        ' Count the rows and
                                                    ' subtract the header.

        Selection.AutoFilter 8, status
        Selection.AutoFilter 9, dcode
        If week2 = "<=" Then
            Selection.AutoFilter 13, startweek
            Selection.AutoFilter 13, week1, xlAnd, week2
        End If
        matched_criteria = 0                        ' Set variable to zero.
        check_row = 0                               ' Set variable to zero.
        While Not IsEmpty(ActiveCell)               ' Check to see if row
                                                    ' height is zero.
            ActiveCell.Offset(1, 0).Select
            If ActiveCell.RowHeight = 0 Then
                check_row = check_row + 1
                matched_criteria = matched_criteria + 1
            End If

        If row_count = check_row Then               ' If these are equal,
                                                    ' nothing was returned.
            MsgBox "No matching data were found.", vbExclamation
            Sheets("Summary").Range("F21").Value = 0
            MsgBox "The total count for these criteria is " & matched_criteria - 1
    ' Display the number of records returned
        End If
        Sheets("Summary").Range("F15").Value = startweek
        Sheets("Summary").Range("F16").Value = endweek
        Sheets("Summary").Range("F18").Value = status
        Sheets("Summary").Range("E21").Value = dcode

        Sheets("Summary").Range("F21").Value = _
            matched_criteria - 1
        Application.ScreenUpdating = True


    Wednesday, January 31, 2007 9:37 PM
  • Thanks for posting the code. I am going to toy with it at work tomorrow. The concept is interesting.
    Wednesday, January 31, 2007 11:36 PM