SOLVED

Help with date sorting macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1503577%22%20slang%3D%22en-US%22%3EHelp%20with%20date%20sorting%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1503577%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20to%20get%20some%20help%20on%20a%20macro%20in%20order%20to%20sort%20an%20entire%20worksheet%20by%20date.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20multiple%20rows%20and%20the%20first%20column%20is%20the%20date%20(using%20a%20date%20and%20time%20picker%20macro).%20People%20will%20be%20going%20in%20and%20filling%20out%20the%20spreadsheet%2C%20but%20with%20each%20row%20of%20information%2C%20the%20date%20of%20that%20item%20may%20change%20to%20a%20later%20date.%20I%20want%20to%20have%20a%20macros%20where%20by%20when%20the%20date%20of%20a%20row%20is%20changed%20in%20column%20A%2C%20it%20moves%20the%20entire%20row%20up%20or%20down%20to%20order%20it%20in%20the%20order%20of%20the%20date.%20Does%20this%20make%20sense%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20one%20macro%20I%20found%20is%20below%2C%20but%20I'm%20not%20sure%20how%20to%20change%20it%20in%20order%20to%20read%20the%20entire%20sheet%20and%20move%20the%20rows.%20I%20also%20already%20have%20a%20date%20and%20time%20picker%20macro%20in%20the%20sheet%2C%20so%20wondering%20would%20I%20just%20paste%20this%20below%20it%3F%20Any%20help%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22line%20number1%20index0%20alt2%22%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EAs%20Range)%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number2%20index1%20alt1%22%3E'Updateby%20Extendoffice%2020160606%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number3%20index2%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BOn%20Error%20Resume%20Next%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number4%20index3%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIf%20Application.Intersect(Target%2C%20Application.Columns(1))%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EIs%20Nothing%20Then%20Exit%20Sub%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number5%20index4%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIf%20Target.Count%20%26gt%3B%201%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3EThen%20Exit%20Sub%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number6%20index5%20alt1%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BRange(%22A1%22).Sort%20Key1%3A%3DRange(%22A2%22)%2C%20Order1%3A%3DxlAscending%2C%20Header%3A%3DxlYes%2C%20_%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number7%20index6%20alt2%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BOrderCustom%3A%3D1%2C%20MatchCase%3A%3DFalse%2C%20Orientation%3A%3DxlTopToBottom%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number8%20index7%20alt1%22%3EEnd%20Sub%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number8%20index7%20alt1%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22line%20number8%20index7%20alt1%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1503577%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1504073%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20date%20sorting%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504073%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713991%22%20target%3D%22_blank%22%3E%40stephanieporter%3C%2FA%3E%26nbsp%3BHey%20I%20have%20created%20a%20simpler%20Macro%20you%20can%20try%20this%20Macro%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Macro2()%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Macro2%20Macro%3CBR%20%2F%3E'%3C%2FP%3E%3CP%3E'%3CBR%20%2F%3ESheets(%22Sheet1%22).Select%3CBR%20%2F%3ECells.Select%3CBR%20%2F%3ESelection.AutoFilter%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).AutoFilter.Sort.SortFields.Clear%3CBR%20%2F%3EActiveWorkbook.Worksheets(%22Sheet1%22).AutoFilter.Sort.SortFields.Add%20Key%3A%3DRange%20_%3CBR%20%2F%3E(%22A1%3AA1048576%22)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlAscending%2C%20DataOption%3A%3D%20_%3CBR%20%2F%3ExlSortNormal%3CBR%20%2F%3EWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).AutoFilter.Sort%3CBR%20%2F%3E.Header%20%3D%20xlYes%3CBR%20%2F%3E.MatchCase%20%3D%20False%3CBR%20%2F%3E.Orientation%20%3D%20xlTopToBottom%3CBR%20%2F%3E.SortMethod%20%3D%20xlPinYin%3CBR%20%2F%3E.Apply%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ERange(%22A1%22).Select%3CBR%20%2F%3ESelection.AutoFilter%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20also%20attaching%20the%20file%20you%20can%20check%20whether%20it%20is%20running%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507817%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20date%20sorting%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507817%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699938%22%20target%3D%22_blank%22%3E%40DevendraJain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20have%20another%20Macro%20on%20the%20worksheet%2C%20do%20I%20just%20copy%20and%20paste%20this%20underneath%20it%20in%20the%20coding%20section%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507832%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20date%20sorting%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699938%22%20target%3D%22_blank%22%3E%40DevendraJain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%20so%20not%20sure%20it's%20working.%20The%20code%20thats%20currently%20in%20my%20work%20book%20(for%20column%20a%20to%20have%20date%20and%20time%20pickers%20in%20it)%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EPrivate%20Sub%20DTPicker1_CallbackKeyDown(ByVal%20KeyCode%20As%20Integer%2C%20ByVal%20Shift%20As%20Integer%2C%20ByVal%20CallbackField%20As%20String%2C%20CallbackDate%20As%20Date)%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3EWith%20Sheet1.DTPicker1%3CBR%20%2F%3E.Height%20%3D%2020%3CBR%20%2F%3E.Width%20%3D%2020%3CBR%20%2F%3EIf%20Not%20Intersect(Target%2C%20Range(%22A115%3AA120%2CA122%3AA131%22))%20Is%20Nothing%20Then%3CBR%20%2F%3E.Visible%20%3D%20True%3CBR%20%2F%3E.Top%20%3D%20Target.Top%3CBR%20%2F%3E.Left%20%3D%20Target.Offset(0%2C%201).Left%3CBR%20%2F%3E.LinkedCell%20%3D%20Target.Address%3CBR%20%2F%3EElse%3CBR%20%2F%3E.Visible%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20With%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20need%20something%20to%20sort%20all%20the%20rows%20by%20date%20should%20we%20decide%20to%20change%20the%20date%20on%20something%2C%20I%20want%20it%20to%20automatically%20move%20all%20the%20content%20and%20align%20them%20in%20date%20order%20(if%20possible).%20There%20are%2017%20columns%20of%20information%20that%20all%20needs%20to%20stick%20together.%20Am%20I%20making%20sense%3F%20I%20can't%20post%20a%20copy%20of%20the%20work%20book%20as%20it's%20confidential%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507945%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20date%20sorting%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F713991%22%20target%3D%22_blank%22%3E%40stephanieporter%3C%2FA%3E%26nbsp%3BPlease%20check%20the%20following%20video%20and%20following%20excel%20whether%20I%20understood%20correctly%20what%20you%20wanted%20to%20say.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20still%20if%20you%20have%20any%20query%20in%20VBA%20then%20you%20can%20also%20message%20me%20privately%20or%20on%20my%20mail%20id%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20if%20you%20liked%20my%20answer%20please%20do%20mark%20my%20answer%20as%20best%20answer%20officially%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi all, 

 

Hoping to get some help on a macro in order to sort an entire worksheet by date. 

I have multiple rows and the first column is the date (using a date and time picker macro). People will be going in and filling out the spreadsheet, but with each row of information, the date of that item may change to a later date. I want to have a macros where by when the date of a row is changed in column A, it moves the entire row up or down to order it in the order of the date. Does this make sense? 

 

The one macro I found is below, but I'm not sure how to change it in order to read the entire sheet and move the rows. I also already have a date and time picker macro in the sheet, so wondering would I just paste this below it? Any help? 

 

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
    On Error Resume Next
    If Application.Intersect(Target, Application.Columns(1)) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A1").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
 
 
4 Replies
Highlighted

@stephanieporter Hey I have created a simpler Macro you can try this Macro

 

Sub Macro2()
'
' Macro2 Macro
'

'
Sheets("Sheet1").Select
Cells.Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("A1:A1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Selection.AutoFilter
End Sub

 

I am also attaching the file you can check whether it is running

Highlighted

Thank you! @DevendraJain 

 

If I have another Macro on the worksheet, do I just copy and paste this underneath it in the coding section? 

Highlighted

@DevendraJain 

 

Ok so not sure it's working. The code thats currently in my work book (for column a to have date and time pickers in it) is: 


Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

With Sheet1.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("A115:A120,A122:A131")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With

End Sub

 

I now need something to sort all the rows by date should we decide to change the date on something, I want it to automatically move all the content and align them in date order (if possible). There are 17 columns of information that all needs to stick together. Am I making sense? I can't post a copy of the work book as it's confidential information. 

 

Thanks

Highlighted
Best Response confirmed by stephanieporter (Occasional Contributor)
Solution

@stephanieporter Please check the following video and following excel whether I understood correctly what you wanted to say. 

 

And still if you have any query in VBA then you can also message me privately or on my mail id

 

And if you liked my answer please do mark my answer as best answer officially