Good afternoon, I would like to know how I do to sort by date and time for excel. It would be 1 hour column and one date column. My question is how do I sort the schedules chronologically without affecting the dates ??
Here's the example of how I'd like the data to be:
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 |
+------------+-------+
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
+------------+-------+
| 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 out this answer
The Method .SortField was used, where the use is made to sort the data according to classification fields.
The difference from .SortField to .Sort is that .Sort is limited to a maximum of three fields, .SortField can perform by more than three. In this case it only took two, but it is a code that is good to be learned in this way, because it can meet all needs.
Set ws = ThisWorkbook.Worksheets("Planilha1")
Declare the worksheet named "Sheet1" as variable ws
UltimaLinha = ws.UsedRange.Rows.Count
Returns the number of the last line of the Worksheet ws
.SortFields.Add Key:=Range("C1:C" & UltimaLinha), Order:=xlAscending
Adds a sort filter in column C from the first line to the last line
.SetRange Range("C1:D" & UltimaLinha)
Set the filtering range of% from% to% with%.
You can use the custom sort function by hierarchical levels:
