VBA sort row by dates

Question

I have a file containing dates on the first row, and a column of identifiers defining a table where I fill in the employee data for the corresponding data (See picture)

I want the dates to be sorted in the chronological order, but I only want to sort the dates, before I fill in the table.

I tried the following code

Sub sortByDates()

    Dim sumSheet as Worksheet
    Dim toSort as Range

    Set sumSheet = ThisWorkbook.ActiveSheet
    LastCol = sumsheet.Cells.Find(What:="*", After:=[a1], _
              SearchOrder:=xlByColumns, 
              SearchDirection:=xlPrevious).Column
    Set toSort = sumsheet.Rows(1).Resize(1, LastCol - 1).Offset(0, 1)

    toSort.Select
    ActiveWorkbook.Worksheets("Employees").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Employees").Sort.SortFields.Add Key:=toSort, SortOn:=xlSortOnValues, Order:=xlAscending, _
     DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Employees").Sort
        .SetRange toSort
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

However the dates moves but don't order as I wish. I think this might have to do with the fact that Excel Sort doesn't know how to handle the dates format, but not sure.

With exception for column B, they are sorted. I would guess that Excel interpreted B1 as a header. Try setting .Header = xlNo intstead of xlGuess — Sam
– Sam, Commented Mar 14, 2019 at 9:15
Column F is also not sorted, as it corresponds to March and appear after June — 0livier1O1
– 0livier1O1, Commented Mar 14, 2019 at 9:35

Kemal AL GAZZAH · Accepted Answer · 2019-03-14 14:29:07Z

1

You should define the format of the data to be sorted. After this line in your code, toSort.Select, add this command:

Selection.NumberFormat = "[$-409]d-mmm-yy;@"

(change the format with appropriate one you use)

Here is the code I used and tested: I got the columns sorted correctly (change feuil1 to your sheet name)

Sub sortByDates()

Dim sumSheet As Range
Dim toSort As Range

'Set sumSheet =
LastCol = ThisWorkbook.ActiveSheet.Cells.Find(What:="*", After:=[a1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set toSort = ThisWorkbook.ActiveSheet.Rows(1).Resize(1, LastCol - 1).Offset(0, 1)

toSort.Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
ActiveWorkbook.Worksheets("feuil1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("feuil1").Sort.SortFields.Add Key:=toSort, SortOn:=xlSortOnValues, Order:=xlAscending, _
 DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("feuil1").Sort
    .SetRange toSort
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlLeftToRight
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

edited Mar 14, 2019 at 14:29

answered Mar 14, 2019 at 9:12

Kemal AL GAZZAH

1,0578 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

0livier1O1 Over a year ago

Thank you for the response. However this doesn't seem to have any effect.

Kemal AL GAZZAH Over a year ago

Please take a look at the code I juste posted, it worked for me

Collectives™ on Stack Overflow

VBA sort row by dates

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related