Using Autofilter in VBA with a range of criteria

Using Autofilter in VBA with a range of criteria

• Muhammad Azeem Azam

  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

  
  
  

• Shirvo

  Thanks for posting the code. I am going to toy with it at work tomorrow. The concept is interesting.
  
  

• KitGreen

  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

  
  
  
• Using Autofilter in VBA with a range of criteria