none
Excel vba on advance filter RRS feed

  • Question

  • HI all,

    i have used excel to record a macro for a list of step of advance filter.  when i finished all my "click" steps to perform the advance filter, the result is correct.

    But when i take the corresponding VBA code and re-run, it does't work , seems that it is no filter result.

    i am doing advance filter on a date field, is the problem related to it?

    the code i generated is listed below.

        Range("A7:P916").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Range("'Ad. filter'!Criteria"), Unique:=False

    Data format of date is "14/9/2010", and i have set the Criteria as >=15/2/2011 and <=14/2/2012

    i dunno why i can't generated the correct result , pls help

    THANKS

    Tuesday, February 14, 2012 3:54 AM

Answers

  • Hello Simon Chan,

    I have only just seen this post or I would have answered it before.

    The reason that the Advanced filter does not work is that VBA cannot handle d/m/y date formats in the Criteria. This occurs for both AdvancedFilter and AutoFilter. (No problem with the main data; just the Criteria.) As you probably know, dates are really stored in the system as numbers and the VBA processes behind the scenes have to convert the string date to a numeric before the criteria can be processed. (Note that the Criteria dates  >=15/2/2011 and <=14/2/2012 are strings; not real dates.) The processes behind the scenes have not been programmed to take the regional date format into account and hense when processing the Criteria, they do not process d/m/y dates correctly.

    There are 2 ways around the problem.

    The first one is to insert the Criteria dates in m/d/y format like the following and the process converts these without problems.

    >=2/15/2011 and <=2/14/2012

    The other method is is to use the alpha abbreviation for the month and the process recognizes the correct date. (This is my preferred solutiuon because using the previous method it causes confusion with dates where the day is 12 or less.)

    >=15 Feb 2011 and <=14 Feb 2012

    There has been forum discussion previously regarding the d/m/y dates with VBA and AutoFilter Criteria. Recording the setting of AutoFilter actually records the Criteria dates in d/m/y format but if you try to run the recorded code, the Criteria is not recognezed but edit the code so the Criteria date to m/d/y format or use the alpha abbreviation for the month and it works. However, I cannot recall previous forum discussion with AdvancedFilter so I have tested this solution and I am confident that it will solve your problem.


     


    Regards, OssieMac


    Wednesday, February 22, 2012 7:10 AM
  • Hi Learning and Learning,
    welcome to the MSDN forum!
    Please refer to the following code:

    Sub Macro1()
    Dim rng1, rng2, rng3 As Range
    Set rng1 = Range("A1:B5")
    Set rng2 = Range("E2:F3")
    Set rng3 = Range("A9")
    
    ' In Copy mode
    'rng1.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rng2, CopyToRange:=rng3
    
    'In Replace mode
    rng1.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rng2, Unique:=False
    End Sub

    Below are the illustration of the Excel sheet:

    Would you please new an excel and give the code I provided a try?

    Thanks.
    Best Regards,
    yoyo

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 1:55 AM
    Moderator

All replies

  • I suppose you have problem in below portion.

    Range("'Ad. filter'!Criteria"), Unique:=False

    Check in workbook whether criteria name refers to range at AD Filter sheet's range or not.(In formulas tab ->NAme Manager->Check the refers to column)

    If the range refers to Ad Filter you need not use sheet reference before name.Directly use:

    Range("Criteria"), Unique:=False

    Tuesday, February 14, 2012 5:58 AM
    Answerer
  • Thanks for yr reply.

    i checked the criteria, it is in order.

    Then i am trying to use "Range("A1:B2")" instead of "Range("'Ad. filter'!Criteria")", it is still not work.

    Besides, till now,  i have one more findings, before i used advance filter to filter a date field, i just tried to filter a numeric field only, then it is work. so maybe the problem is on the "Date", but i still can solve the problem.

    PLs help.

    THANKS

    Tuesday, February 14, 2012 6:33 AM
  • Hmm.Not sure but try to refer the criteria in same format as in original range.

    If in range it is mm-dd-yy then in criteria range put in same way.

    Tuesday, February 14, 2012 7:14 AM
    Answerer
  • Hi Learning and Learning,
    welcome to the MSDN forum!
    Please refer to the following code:

    Sub Macro1()
    Dim rng1, rng2, rng3 As Range
    Set rng1 = Range("A1:B5")
    Set rng2 = Range("E2:F3")
    Set rng3 = Range("A9")
    
    ' In Copy mode
    'rng1.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rng2, CopyToRange:=rng3
    
    'In Replace mode
    rng1.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rng2, Unique:=False
    End Sub

    Below are the illustration of the Excel sheet:

    Would you please new an excel and give the code I provided a try?

    Thanks.
    Best Regards,
    yoyo

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Friday, February 17, 2012 1:55 AM
    Moderator
  • Hi Simon,

    Do you have any updates on this issue now?

    If you need any help, please feel free to let us know.

    Have a nice day!

    yoyo


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, February 22, 2012 2:38 AM
    Moderator
  • Hello Simon Chan,

    I have only just seen this post or I would have answered it before.

    The reason that the Advanced filter does not work is that VBA cannot handle d/m/y date formats in the Criteria. This occurs for both AdvancedFilter and AutoFilter. (No problem with the main data; just the Criteria.) As you probably know, dates are really stored in the system as numbers and the VBA processes behind the scenes have to convert the string date to a numeric before the criteria can be processed. (Note that the Criteria dates  >=15/2/2011 and <=14/2/2012 are strings; not real dates.) The processes behind the scenes have not been programmed to take the regional date format into account and hense when processing the Criteria, they do not process d/m/y dates correctly.

    There are 2 ways around the problem.

    The first one is to insert the Criteria dates in m/d/y format like the following and the process converts these without problems.

    >=2/15/2011 and <=2/14/2012

    The other method is is to use the alpha abbreviation for the month and the process recognizes the correct date. (This is my preferred solutiuon because using the previous method it causes confusion with dates where the day is 12 or less.)

    >=15 Feb 2011 and <=14 Feb 2012

    There has been forum discussion previously regarding the d/m/y dates with VBA and AutoFilter Criteria. Recording the setting of AutoFilter actually records the Criteria dates in d/m/y format but if you try to run the recorded code, the Criteria is not recognezed but edit the code so the Criteria date to m/d/y format or use the alpha abbreviation for the month and it works. However, I cannot recall previous forum discussion with AdvancedFilter so I have tested this solution and I am confident that it will solve your problem.


     


    Regards, OssieMac


    Wednesday, February 22, 2012 7:10 AM
  • thank you for all the advice.

    Thursday, March 1, 2012 6:59 AM