Forum Discussion

WorkingExcel1100's avatar
WorkingExcel1100
Copper Contributor
Feb 23, 2022

How to filter list after using advanced filter?

Hi, I have managed to extract a particular set of data using advanced data (VBA) but was wondering how can I take it a step further by sorting the extracted data by date with VBA instead of manual data > filter> sorting?

 

E.g.

Customer A bought 10 on 10-Jan

Customer B bought 10 on 11-Jan

Customer C bought 10 on 9-Jan

 

When using advanced filter, the sequence will remain in order of the cells (A B C). I was hoping for the VBA to sort CAB before copy the data to the "CopyTo" range -> according to date of transaction. I have managed to add in a timestamp to when the data is entered. Do let me know if you have an idea. Thanks!

 

excel
Macros and VBA
office 365

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 23, 2022

    WorkingExcel1100 

     

    Maybe as an additional code

     

    ActiveSheet.ListObjects("Table_from_Database ").Range. _
AutoFilter Field:=5, Criteria1:=">=" & CLng(CDate("01.02.2022")), Operator:=xlAnd, Criteria2:="<=" & CLng(CDate("23.02.2022"))

     

     

    or as a macro

     

    Option Explicit

Sub Filtern()
   Dim vDat As Variant
   vDat = InputBox( _
      prompt:="Date:", _
      Default:=Format(Date + 6, "dd.mm.yy"))
   If vDat = "" Then Exit Sub
   vDat = CDate(vDat)
   Range("A1").CurrentRegion.AutoFilter _
      field:=3, Criteria1:=">=" & CDbl(vDat)
End Sub

     

    Example file for this macro is included.

     

    I'm not sure if this is what you're looking for, but I hope this may be of some help.
     

    NikolinoDE

    I know I don't know anything (Socrates)

     

     

Resources