none
Using Autofilter in VBA with a range of criteria

    Question

  • 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
        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!

    Thanks.

    JS

    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
         
        Sheets("Prod").Select
        Range("a1").Select
        Selection.CurrentRegion.Select
        row_count = Selection.Rows.Count - 1        ' Count the rows and
                                                    ' subtract the header.

        Selection.AutoFilter
        Selection.AutoFilter 8, status
        Selection.AutoFilter 9, dcode
        If week2 = "<=" Then
            Selection.AutoFilter 13, startweek
        Else
            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
            Else
                matched_criteria = matched_criteria + 1
            End If
        Wend

        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
        Else
            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
           
       
        Range("A:M").AutoFilter
        Sheets("Summary").Range("F21").Select
       
       
        Application.ScreenUpdating = True

    /js

    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