+ Reply to Thread
Results 1 to 19 of 19

Advanced Filter not working with VBA, but works manually.

  1. #1
    Registered User
    Join Date
    11-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    6

    Question Advanced Filter not working with VBA, but works manually.

    Hi, I have attached a excel file with data that is to be filtered. There is one peculiar problem in the advanced filter. First i filter a set of records based on a criteria which works perfect. Then i need to filter a particular column for unique values from the previously extracted data, this is not working. The peculiar problem is it works for one particular column alone whereas for others it just displays the header part alone. Can anyone help me out on this...
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    Both the filters works good for me.

    If you think you have something wrong, turn on the macro recorder and record the filter action manually and then check for any differences between what recorded and what you have.
    HTH
    Regards, Jeff

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    I did some more researching on this and did get it to mess up the way you described.

    Here is what I found as a fix.

    http://spreadsheetpage.com/index.php...er_dialog_box/

  4. #4
    Registered User
    Join Date
    11-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Advanced Filter not working with VBA, but works manually.

    Mr. Jefrrey,

    Thanks for your reply...
    I tried recording the macro, i could not find any difference between the recorded macro and my code. This is were i am stuck up, it does not work for me. For your further reference i have attached the actual file that i am working, you can see the sheet1 in this file.

    Thanx
    Sundar
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: Advanced Filter not working with VBA, but works manually.

    The first advanced filter creates named ranges "Criteria" and "Extract". If you delete these ranges by adding the lines below at the beginning of the second macro, it works correctly.
    Please Login or Register  to view this content.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    @Huron,

    Did you actually run that in the posted workbook?

    I tried it earlier but it debugs on...

    Please Login or Register  to view this content.

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Advanced Filter not working with VBA, but works manually.

    I think it should be

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  8. #8
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: Advanced Filter not working with VBA, but works manually.

    It works ok for me with the code I posted - see the attached file


    Edited to add
    The code will only work if you run the first macro before the second one - otherwise the named ranges won't exist.
    Attached Files Attached Files
    Last edited by Huron; 12-29-2012 at 10:33 PM.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    That did it Mike...Thanks.

    On John Walkenbach's site (like above), he uses ActiveWorkbook.

    Do you think this has anything to do with 2003 versus 2007?

    I can't see why as I'm running 2007 and for one brief moment I got his (John Walkenbach) code to run.

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    @Huron,

    Out of curiosity, what happens if you press Filter Button 2 a second time?

    I get a run time error which debugs to that same line whether it is worksheet or workbook.

  11. #11
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: Advanced Filter not working with VBA, but works manually.

    I get an error if I press it a second time - perhaps because Criteria no longer exists as it's not used in the second advanced filter.

  12. #12
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Advanced Filter not working with VBA, but works manually.

    No if you look in the name manager there are 2 reference for Criteria and Extract. 1 for Sheet1 and 1 for Mastersheet. I believe that to be the issue. Huron Activeworkbook works because he only has one reference to those names. Also if you click button 2 twice in Huron wb it will error. I think you can just add the below code to Macro 4 in the OP's wb.
    EDIT: Add to the bottom of Macro 4
    Please Login or Register  to view this content.
    Last edited by mike7952; 12-29-2012 at 10:42 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: Advanced Filter not working with VBA, but works manually.

    It might be better to delete the second advanced filter and just copy and paste the values and then remove duplicates
    Please Login or Register  to view this content.
    then there shouldn't be a problem!

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    Ok, got it. Sorry I'm a little dense.

    Placed...

    Please Login or Register  to view this content.
    ...at the bottom of macro4 as you said Mike and nothing on the second macro.

    All works grand. Thanks for hanging in there with me...

  15. #15
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Advanced Filter not working with VBA, but works manually.

    No problem Jeff. Have a safe and Happy New Year

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Advanced Filter not working with VBA, but works manually.

    And you too Mike.

  17. #17
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: Advanced Filter not working with VBA, but works manually.

    @ mike7952,

    Well spotted, Mike!

  18. #18
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Advanced Filter not working with VBA, but works manually.

    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  19. #19
    Registered User
    Join Date
    11-28-2012
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Advanced Filter not working with VBA, but works manually.

    Thanks to all Jefrrey, Huron, Mike for your reply. Based on your idea i found a link giving a different solution which worked out for me. Anyhow the idea is yours, so the credit goes to you all. See below the update code. The link has suggested to use vbNullString in criteria for the second macro which makes it working.

    Please Login or Register  to view this content.
    Thanks to all and wish you Happy new year

    Regards
    Sundar
    Last edited by jeffreybrown; 12-31-2012 at 08:58 AM. Reason: Please use code tags...Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1