Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CriteriaRange (03)

    Can the advanced filter CriteriaRange be defined in code? I know that one can define the CriteriaRange to particular cells in a worksheet such as CriteriaRange: Sheets("Sheet1").Range("A1:B2"). I don't know if one is bound to defining the range to cells which may be a limitation of Excel.

    Thanks,
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CriteriaRange (03)

    I don't understand. What do you want the range to be if not cells? It needs to be a range object which is composed of cells.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CriteriaRange (03)

    Steve,

    I thought it may be possible to define the range as a variable in code without a range object which is composed of cells.

    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CriteriaRange (03)

    It can be a variable. the variable must be a range or a string representing a range or a range name:

    A very simple example:

    Dim rng As Range
    Dim sRng As String
    Set rng = Range("i1:i2")
    sRng = "H1:H2"
    Range("A1:C8").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=rng, _
    Unique:=False
    Range("A1:C8").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Range(sRng), _
    Unique:=False

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>What do you want the variable to contain?
    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CriteriaRange (03)

    Nope, you really need to pass a valid range of cells, literals are not permitted.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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