PDA

View Full Version : Help with autofilter



circaa
07-07-2006, 01:14 PM
Hi

On my worksheet, the column A contains dates. I want to create a research between 2 dates. I know the autofilter already contains an option(Personalised) that can do the job but you have to select two dates already in the listbox and they're not sorted so it's painfull.

1) I want the user to enter the 2 dates on a form, and when the button search is clicked, I want to sort all my rows(by column A) with the autofilter "increasing"

2)Once this is done, I could loop through all my rows and hide the ones that are before the first date, keep the ones between and then hide the ones after the second date.

So I wanna know if there's a better way to do that...and if not...I need help to autofilter the first row by vba.

Thanks

Joey

Zack Barresse
07-07-2006, 01:27 PM
Yes, you can do this. The only question is how are the dates entered in a userform? If they are not Excel recognized dates, then the answer is no. What I generally recommend is either using a Calendar control (only for local machines) or, for the majority of the time, three combo boxes, one for the month, one for the day, one for the year. When you hit your button you can create the date using the DateSerial function.

If you need more, try posting a sample workbook.

circaa
07-07-2006, 02:13 PM
Thanks Zack

Ok now how do i get the rows to be sorted increasing the dates by the code under the commandbutton ??

thanks

Zack Barresse
07-10-2006, 12:57 PM
What do you mean? Are you asking how to Sort via VBA?

circaa
07-11-2006, 05:15 AM
Exactly....But I've been searching and shouldn't I be able to do what I wanna do with criteria1, criteria2 of the autofilter method? I tried it but it won't work...even with excel recognized date formats.

Anyway how do I sort increasingly with my code ??

thanks

Joey

Zack Barresse
07-11-2006, 02:50 PM
Yes, you should be able to use autofilter with two criteria. Here is a Sorting VBA line ...

With Sheets(mysheet)
.Range(myrange).Sort key1:=.Range(mysortcol), order1:=xlascending
End With

.. autofilter you should be able to use like so ..

With Sheets(mysheet)
.Range(myrange).autofilter field:=colnum, criteria1:=">1/1/2007", operator:=xland, criteria2:="<1/1/2008"
End With

Does this help?