Change Data Time Format and Identify last row.

[LeanneBG]

Hi Experts - I have below code that sorts the data from column A to K based on the date and time value found in column I.. the code works, however, i feel like i can do better on this part of the code - "I1:I2000" My number of my rows are not fixed and is changing.. so not sure how to do this.. Also sometimes the table has no data so i want my code to not end in error in case there's no data..

Also, before sorting the date and time, i need to change the format first from 2019-03-15 11:52:18 into this format 15/03/2019 11:41:10 AM. Again the number of rows is not fixed so i dont have the definite last row for it.. but i need to change the format of the whole column I until the row with the last data..
Thanks in advance for those who will help..

Sub Sort()
    Sheets("SRPT").Select
    ActiveSheet.AutoFilterMode = False
    
    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "I1:I2000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
End Sub

 

[LeanneBG]

Hi LeanneBG,

How are your dates formatted?

As dates or as text?

if stored as text you will have to convert them to dates first.

Sometimes you can identity dates as text (Provided you have'nt right aligned the cells first) if left aligned store as text, if right aligned stored as dates) when imported data from a file or database they come in as text and not dates.

What are yours stored as?

Everytime i manually change the format to dates (long or short), nothing is happening.. It is only changing to the format i want everytime i click after the last number and click enter..

 

[Brombrough]

Hi LeanneBG,

There is a good article, Hyperlink below that explains dates and text dates and on how to fix the problem

https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/

if the origin of the data is imported suggest you format the dates on import rather than try to amend afterwards.



Hope this helps.