How to filter list after using advanced filter?

%3CLINGO-SUB%20id%3D%22lingo-sub-3202163%22%20slang%3D%22en-US%22%3EHow%20to%20filter%20list%20after%20using%20advanced%20filter%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3202163%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20have%20managed%20to%20extract%20a%20particular%20set%20of%20data%20using%20advanced%20data%20(VBA)%20but%20was%20wondering%20how%20can%20I%20take%20it%20a%20step%20further%20by%20sorting%20the%20extracted%20data%20by%20date%20with%20VBA%20instead%20of%20manual%20data%20%26gt%3B%20filter%26gt%3B%20sorting%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EE.g.%3C%2FP%3E%3CP%3ECustomer%20A%20bought%2010%20on%2010-Jan%3C%2FP%3E%3CP%3ECustomer%20B%20bought%2010%20on%2011-Jan%3C%2FP%3E%3CP%3ECustomer%20C%20bought%2010%20on%209-Jan%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20using%20advanced%20filter%2C%20the%20sequence%20will%20remain%20in%20order%20of%20the%20cells%20(A%20B%20C).%20I%20was%20hoping%20for%20the%20VBA%20to%20sort%20CAB%20before%20copy%20the%20data%20to%20the%20%22CopyTo%22%20range%20-%26gt%3B%20according%20to%20date%20of%20transaction.%20I%20have%20managed%20to%20add%20in%20a%20timestamp%20to%20when%20the%20data%20is%20entered.%20Do%20let%20me%20know%20if%20you%20have%20an%20idea.%20Thanks!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3202163%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3206125%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20filter%20list%20after%20using%20advanced%20filter%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3206125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1315224%22%20target%3D%22_blank%22%3E%40WorkingExcel1100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EMaybe%20as%20an%20additional%20code%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EActiveSheet.ListObjects(%22Table_from_Database%20%22).Range.%20_%0AAutoFilter%20Field%3A%3D5%2C%20Criteria1%3A%3D%22%26gt%3B%3D%22%20%26amp%3B%20CLng(CDate(%2201.02.2022%22))%2C%20Operator%3A%3DxlAnd%2C%20Criteria2%3A%3D%22%26lt%3B%3D%22%20%26amp%3B%20CLng(CDate(%2223.02.2022%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Eor%20as%20a%20macro%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EOption%20Explicit%0A%0ASub%20Filtern()%0A%20%20%20Dim%20vDat%20As%20Variant%0A%20%20%20vDat%20%3D%20InputBox(%20_%0A%20%20%20%20%20%20prompt%3A%3D%22Date%3A%22%2C%20_%0A%20%20%20%20%20%20Default%3A%3DFormat(Date%20%2B%206%2C%20%22dd.mm.yy%22))%0A%20%20%20If%20vDat%20%3D%20%22%22%20Then%20Exit%20Sub%0A%20%20%20vDat%20%3D%20CDate(vDat)%0A%20%20%20Range(%22A1%22).CurrentRegion.AutoFilter%20_%0A%20%20%20%20%20%20field%3A%3D3%2C%20Criteria1%3A%3D%22%26gt%3B%3D%22%20%26amp%3B%20CDbl(vDat)%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EExample%20file%20for%20this%20macro%20is%20included%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EI'm%20not%20sure%20if%20this%20is%20what%20you're%20looking%20for%2C%20but%20I%20hope%20this%20may%20be%20of%20some%20help.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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!

 

1 Reply

@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)