VBA-advanced filter with multiple criteria

Hi Kazdima,

Your sample has no data so it can't be tested

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

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

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.

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

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.

@ Leo,
Nice AutoFilter Alternative

@kazdima,
I think I have the Advanced Filter version working - see post # 15

What's the problem ?

Originally Posted by snb

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!!

Originally Posted by LeoTaxi

@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

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 <=

@ 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

.....

Originally Posted by snb

......
Of course you can't use validation if you want to enter > >= < or <=

of course...