Sort chronologically VBA date and time

Question

Sort chronologically VBA date and time

Good afternoon, I would like to know how I do to sort by date and time by excel. It would be 1 column of schedule and another of date. Oh my doubt is how do I order the schedules chronologically without affecting the dates??

Follows the example of how I would like the data to look:

1

excel vba macro

Author: Daniel Kenzi, 2018-03-15

Source

2 answers

You can use the custom sorting function by hierarchical levels:

0

Author: virtualdvid, 2018-03-16 00:04:35

score 0 · Accepted Answer

Example:

With these test data:

+------------+-------+
| 28/02/2012 | 10:00 |
| 19/02/2015 | 09:00 |
| 22/02/2015 | 04:32 |
| 01/03/2017 | 08:00 |
| 19/01/2018 | 01:00 |
| 19/01/2018 | 15:00 |
| 13/02/2018 | 09:00 |
| 16/02/2015 | 12:00 |
| 13/06/2013 | 15:00 |
| 13/06/2013 | 11:00 |
+------------+-------+

Code

Dim ws As Worksheet
Dim UltimaLinha As Long
Set ws = ThisWorkbook.Worksheets("Planilha1")
UltimaLinha = ws.UsedRange.Rows.Count

With ws.Sort
 .SortFields.Clear
 .SortFields.Add Key:=Range("C1:C" & UltimaLinha), Order:=xlAscending
 .SortFields.Add Key:=Range("D1:D" & UltimaLinha), Order:=xlAscending
 .SetRange Range("C1:D" & UltimaLinha)
 .Header = xlNo
 .Apply
End With

Result

+------------+-------+
| 28/02/2012 | 10:00 |
| 13/06/2013 | 11:00 |
| 13/06/2013 | 15:00 |
| 16/02/2015 | 12:00 |
| 19/02/2015 | 09:00 |
| 22/02/2015 | 04:32 |
| 01/03/2017 | 08:00 |
| 19/01/2018 | 01:00 |
| 19/01/2018 | 15:00 |
| 13/02/2018 | 09:00 |
+------------+-------+

For more information about the last line, check this answer

Explanation

.SortField

Or Method .SortField was used, in which the use is made to sort the data according to sorting fields.

The difference from .SortField to .Sort is that .Sort is bounded by at most three field, already the .SortField can perform in more than three. In this case only two were needed, however, it is a code that is good to be learned in this way, since it can meet all needs.

Ws

Set ws = ThisWorkbook.Worksheets("Planilha1")

Declares the worksheet named "worksheet 1" as variable ws

Last Line

UltimaLinha = ws.UsedRange.Rows.Count

Returns the number of the last row of the sheet ws

SortField.Add

.SortFields.Add Key:=Range("C1:C" & UltimaLinha), Order:=xlAscending

Adds a sort filter in Column C from the first row to the last row

SetRange

.SetRange Range("C1:D" & UltimaLinha)

Sets the sort filter range from C1 to D & número da última linha.