Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting criterial for autofilter (2000 SP3)

    My data (which came from an external source via ODBC) is already listed in date order. I wantto autofilter so that the data for that range of dates only is displayed and ideally to autofilter on another field for something else. So far so easy - just use custom autofilters in the date column and the other one. Problem is that I'm not using the sheet - the person who is using it would like to put the relevant information in a few cells and for Excel to magically do the rest. Is there a way I can use VBA to set the criteria for the custom autofilters or is there another way of doing this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting criterial for autofilter (2000 SP3)

    You could create a command button that executes a macro with code such as this:

    Sub SetFilter()
    If Range("B21") = "" Then
    If Range("B22") = "" Then
    ActiveSheet.AutoFilterMode = False
    Else
    Range("A1").CurrentRegion.AutoFilter _
    Field:=1, _
    Criteria1:="<=" & Format(Range("B22"), "mm/dd/yyyy")
    End If
    ElseIf Range("B22") = "" Then
    Range("A1").CurrentRegion.AutoFilter _
    Field:=1, _
    Criteria1:=">=" & Format(Range("B21"), "mm/dd/yyyy")
    Else
    Range("A1").CurrentRegion.AutoFilter _
    Field:=1, _
    Criteria1:=">=" & Format(Range("B21"), "mm/dd/yyyy"), _
    Operator:=xlAnd, _
    Criteria2:="<=" & Format(Range("B22"), "mm/dd/yyyy")
    End If
    End Sub

    This code expects that the data range starts in cell A1, that the start date is in cell B21 and the end date in cell B22 (both can be empty). See attached workbook.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Hereford, Herefordshire, England
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting criterial for autofilter (2000 SP3)

    Thanks for your help Hans - that was exactly what I needed!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •