Chronologically order VBA date and time

Asked

Viewed 1,488 times

1

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

Follow the example of how I would like the data to stay:

inserir a descrição da imagem aqui

excel vba macro

2 answers

0


Example:

With this 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

Upshot

+------------+-------+
| 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 on the last line, check this answer

Explanation

.Sortfield

The Method .Sortfield was used, where the use is made to sort the data according to classification fields.

The difference of .SortField to the .Sort is that the .Sort is limited to a maximum of three fields, whereas the .SortField can accomplish in more than three. In this case only two were needed, however, it is a code that is good to be learned this way, as it can meet all needs.

Ws

Set ws = ThisWorkbook.Worksheets("Planilha1")

Declares the Worksheet named "Worksheet 1" as variable ws

Ultimalinha

UltimaLinha = ws.UsedRange.Rows.Count

Returns the number of the last line of the Worksheet ws

Sortfield.Add

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

Adds a Sort Filter to the column C from the first line to the last line

Setrange

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

Sets the sorting filter range of C1 to D & número da última linha.

  • Our gave Certinho! Thank you very much! Now I will try to understand what your code says (I am very beginner ^^)

  • All right, thank you very much ^^

0

You can use the custom sort function by hierarchical levels:

Browser other questions tagged excel vba macro

You are not signed in. Login or sign up in order to post.