Consulting

Results 1 to 6 of 6

Thread: Help with autofilter

  1. #1

    Help with autofilter

    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

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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.

  3. #3
    Thanks Zack

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

    thanks

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    What do you mean? Are you asking how to Sort via VBA?

  5. #5
    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

  6. #6
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Yes, you should be able to use autofilter with two criteria. Here is a Sorting VBA line ...

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

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

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

    Does this help?

Posting Permissions

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