Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter with var (2000 sr-1)

    Have this condition to make a filter but insted the real data is possible to assign in criteria a variable... admitting the name of var is data_cont= WS.Range("O1")?
    I have dimensioned the var data_cont as date

    RNG1.AutoFilter Field:=6, Criteria1:=">=17/09/2007"

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

    Re: Filter with var (2000 sr-1)

    Try this:

    RNG1.AutoFilter Field:=6, Criteria1:=">=" & Format(data_cont, "mm/dd/yyyy")

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter with var (2000 sr-1)

    Sorry me Hans but the crieria not filter correct(?!)
    note:
    In other case your format is mm/dd/yyyy and not dd/mm/yyyy for my use

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

    Re: Filter with var (2000 sr-1)

    The code does work correctly (VBA requires the American date format mm/dd/yyyy) but your filter hasn't been set correctly. It applies to only part of the data. If you add a line to turn the filter off, it should work OK:

    ' Turn filter off
    rng1.AutoFilter
    ' Turn filter on again with criteria
    rng1.AutoFilter Field:=6, Criteria1:=">=" & Format(data_cont, "mm/dd/yyyy")

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter with var (2000 sr-1)

    About this question is possible to store in var_MIN and var_MAX the min and max value in visible cells after filter, naturaly in date format?

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

    Re: Filter with var (2000 sr-1)

    I assume that you mean the minimum and maximum of column F. Here is an example:
    <code>
    Sub SetFilter()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim data_cont As Date
    Dim var_min As Date
    Dim var_max As Date
    Set ws = Worksheets("L0953")
    Set rng1 = ws.Range("A2")
    data_cont = ws.Range("O1")
    rng1.AutoFilter
    rng1.AutoFilter Field:=6, Criteria1:=">=" & Format(data_cont, "mm/dd/yyyy")
    Set rng2 = ws.Range("F3:F65536").SpecialCells(xlCellTypeVisib le)
    var_min = Application.Min(rng2)
    var_max = Application.Max(rng2)
    ' Do something with var_min and var_max
    Debug.Print var_min, var_max
    End Sub</code>

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter with var (2000 sr-1)

    Hans tks for fast response but i cannot test now, tomorrow in my office...
    Tks, i tell you about result.

Posting Permissions

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