+ Reply to Thread
Results 1 to 24 of 24

VBA-advanced filter with multiple criteria

  1. #1
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    VBA-advanced filter with multiple criteria

    Hello all,

    I used this site:

    http://www.onlinepclearning.com/exc....tiple_Criteria

    to build my own advanced filter with multiple criteria with my data.

    My data is being copied to a FilterData sheet with the help of Macros and being Cleared also without problems.

    But for some reason, macros with my multiple Criteria is not working.

    My file is big. If someone is ready to help, I can send my file.

    Can anyone help me?

    Thank you.

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: VBA-advanced filter with multiple criteria

    without file not possible

    Kind regards
    Leo

  3. #3
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VBA-advanced filter with multiple criteria

    How can I upload a file?

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: VBA-advanced filter with multiple criteria

    right down Go Advanced
    then with the paper clip

  5. #5
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VBA-advanced filter with multiple criteria


  6. #6
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VBA-advanced filter with multiple criteria

    Can anyone help?

  7. #7
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: VBA-advanced filter with multiple criteria

    sorry no dropbox for me

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: VBA-advanced filter with multiple criteria

    Hi Kazdima,

    Your sample has no data so it can't be tested
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: VBA-advanced filter with multiple criteria

    Thanks for the rep! Will you be posting another sample? If so you can post anything under 1mb right here - no need for the dropbox - you just:

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  10. #10
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VBA-advanced filter with multiple criteria

    Unfortunately, I cannot post a file here. My empty file is 2,770kb, because it has 3 sheets and 2 macros. Otherwise, you won't be able to understand.
    Like I wrote in my first post- I am using exactly the same file like here http://www.onlinepclearning.com/exc....tiple_Criteria
    only my data file is has more columns:
    Date Company location_key Number PATIENT_LAST patient_first First,Last Name ymd_first_service ymd_last_service ymd_next_booking gfap amount

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: VBA-advanced filter with multiple criteria

    Send me a book with just data in the three sheets.

  12. #12
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VBA-advanced filter with multiple criteria

    HI Xladept,
    Pls see separate 3 files like they should be in one book.

    Thank you.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    04-29-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: VBA-advanced filter with multiple criteria

    Did you have a chance to look at my files?

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: VBA-advanced filter with multiple criteria

    Hi Kazdima,

    I've looked at your files, but I can only get the headers to copy over.

    I've done some research on the web and at least one says that the advanced filter can't handle dates

    I'm submitting this thread to the community at large - hopefully someone will know how to solve this.

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA-advanced filter with multiple criteria

    Hi kazdima,
    _ 1) Dates can be a bit tricky... You may get some ideas here:
    http://www.mrexcel.com/forum/excel-q...-cavalry.html?
    I seem to have versions of your code working, but am a bit unsure excactly what is going on with those date comparisons....

    As for the rest:
    I am a bit out of my depth here, but depending on what i understand you actually want to do:
    either:
    _ 2) I can get your code working if i modify your code thus
    Please Login or Register  to view this content.
    ( i think you should just put in your criteria "Block" only the headings that you are applying criteria to. In your CopyToRange you put all the headings you want. ( And make sure the range extends to cover all those headings ) .. see
    http://www.mrexcel.com/forum/excel-q...xls2003-3.html

    OR
    _ 3) If you mean you want to select your criteria from those drop down boxes?,
    _ then Leaving your code as you have it:
    Please Login or Register  to view this content.

    then you need to change your formulas i think to something like this
    Using Excel 2007
    -
    O
    P
    Q
    R
    S
    6
    Company Number First,Last Name gfap amount
    7
    =IF(E6="","="&"*",E6) =IF(F6="","<>0",F6) =IF(G6="","="&"*",G6) =IF(H6="","="&"*",H6) =IF(I6="","<>0",I6)
    FilterData

    Note the important change is for Numbers you need <>0 ( or maybe something similar ?? ) rather than * as you had in all formulas. ( But I confess I do not understand why...) ***** so

    Alan


    EDIT*****
    ... You may want to take up the offer in Post # 16 to get it definitely correct. If so here is a link to the File that is working for me. I would be interested in any further work on it or input from Romperstomper, both to understand better and also as although it seems to work at my end, I see numbers in the CriteriaRange Dates but Dates or sometimes numbers in Date column B ( Both in the sheet "FilterData" ) which confuses me a bit??......
    Here a link you can give him if you have trouble uploading a File: ( This is the File I have working )
    https://app.box.com/s/dx7ev550nbgmnu78uy1agp2dmo25dxxo

    (....It is sent over
    this free File sharing thing )
    Box Net,
    https://www.box.com/
    http://tinyurl.com/7chr7u8
    https://app.box.com/signup/personal
    _ a lot of people use that at MrExcel as you cannot attatch Files there .....)



    ( BTW. The link you gave in Post #10 does not work )

    Alan
    Last edited by Doc.AElstein; 10-16-2015 at 02:22 AM. Reason: <>O not coming out right in Screenshot????
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  16. #16
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: VBA-advanced filter with multiple criteria

    If you can post at Mrexcel with your Dropbox link, I'll have a look tomorrow. Remember to include cross posting links there and here.
    Remember what the dormouse said
    Feed your head

  17. #17
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: VBA-advanced filter with multiple criteria

    Ive changed formulas in filter blok so see if this give right result


    Kind regards
    Leo
    Attached Files Attached Files

  18. #18
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA-advanced filter with multiple criteria

    @ Leo,
    Nice AutoFilter Alternative

    @kazdima,
    I think I have the Advanced Filter version working - see post # 15
    Last edited by Doc.AElstein; 10-16-2015 at 02:36 AM.

  19. #19
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: VBA-advanced filter with multiple criteria

    What's the problem ?
    Attached Files Attached Files



  20. #20
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,379

    Re: VBA-advanced filter with multiple criteria

    @snb

    problem was the possibility to have data between to dates, i think.

    Kind regards
    Leo

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA-advanced filter with multiple criteria

    Quote Originally Posted by snb View Post
    What's the problem ?
    Well, short answer is : Does not answer the question.......

    _ .. My problem is: As often with your codes, It looks a great one liner, but I can’t begin to understand it so sadly it is lost on me, as I Avoid as much as possible using VBA-code what I do not understand. Shame, but that is my problem...

    _ ....The OP’s problem is that it does something only very vaguely similar to what he wants. So it will probably be lost on him as well! –


    _ . There is a lot of knowledge and experience behind that code. But IMO if you do not explain it, then I doubt anyone needing help could modify it to their needs ( unless they have a lot of free time ) : Anyone understanding enough about it to modify it to their needs would be able to write it themselves. So sometimes I wonder what your motives are in Posting?

    _ But thanks anyway , i will add it to the File I have, maybe one day when i have a lot of spare time, I might be able to understand it

    Alan

    P.s. Your web site is great though, I highly recommend that. Arrays are tops!!
    Last edited by Doc.AElstein; 10-16-2015 at 04:17 AM.

  22. #22
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA-advanced filter with multiple criteria

    Quote Originally Posted by LeoTaxi View Post
    @snb

    problem was the possibility to have data between to dates, i think.

    Kind regards
    Leo
    .. and that he selects his criteria from drop down boxes, and that for no criteria all data comes out .... the point with the "*" or "<>0" stuff ....
    Alan
    Last edited by Doc.AElstein; 10-16-2015 at 04:18 AM.

  23. #23
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: VBA-advanced filter with multiple criteria

    You will have to bear in mind that you will have to enter the dates in the filter criteria in US format.

    Of course you can't use validation if you want to enter >, >=, < or <=
    Attached Files Attached Files
    Last edited by snb; 10-16-2015 at 06:03 AM.

  24. #24
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: VBA-advanced filter with multiple criteria

    @ snb....

    _ .. as always, ... bit by bit , ..... i wish i had the time or it was my Thread, then at the end of the day after about 10 replies i would have the working code... but i will leave that to the OP ( in the unlikely event he is so inclined... ) , my small brain is struggling to answer some over Threads just now..

    Alan

    .....

    Quote Originally Posted by snb View Post
    ......
    Of course you can't use validation if you want to enter > >= < or <=
    of course...
    Last edited by Doc.AElstein; 10-16-2015 at 05:05 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Advanced Filter, and multiple <> criteria
    By SILENTBRONCO in forum Excel General
    Replies: 10
    Last Post: 10-12-2015, 10:53 AM
  2. Advanced filter using multiple criteria
    By logan131131 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2012, 11:36 AM
  3. [SOLVED] Advanced Filter: Problem with Multiple AND/OR Criteria
    By excelnewbie80 in forum Excel General
    Replies: 2
    Last Post: 06-18-2012, 09:00 AM
  4. Advanced Filter with multiple criteria
    By inspiresit in forum Excel General
    Replies: 6
    Last Post: 08-12-2010, 11:21 AM
  5. advanced filter through vba with multiple criteria
    By edoylederry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2010, 07:23 AM
  6. Advanced filter on multiple criteria
    By agalloch in forum Excel General
    Replies: 2
    Last Post: 08-19-2009, 09:48 AM
  7. advanced filter macro to locate values via multiple criteria
    By jjfjr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-07-2005, 11:35 PM

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