Change Data Time Format and Identify last row.

L

LeanneBG

Board Regular
Joined
Jun 20, 2016
Messages
157
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..

Code: 
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
James006 said:
Hello,

Your question is very similar to your previous one ...:wink:

https://www.mrexcel.com/forum/excel...t-numbers-ignore-blanks-text.html#post5245151
Click to expand...

Sorry about that, i forgot to mention that I tried to follow the previous help however, it ended in error.. see below code. The error was on highlighted below.. Also can you also help on how to convert the date format from 2019-03-15 11:52:18 into this format 15/03/2019 11:41:10 AM before sorting?
Code: 
Sub Sort()
Dim rng As Range, cell As Range, LastRowInR As Long


Sheets("SRPT").Select
ActiveSheet.AutoFilterMode = False


LastRowInR = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Set rng = ThisWorkbook.Sheets("SRPT").Range("I2:I" & LastRowInR)




 
    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Clear
[U][B]    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Add Key:=Range( _[/B][/U]
[U][B]        rng), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _[/B][/U]
[U][B]        xlSortTextAsNumbers[/B][/U]
    With ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
End Sub
 
Upvote 0
Actually i have two sets of data..columns A to K and columns M to S. I want to re-apply the code above to do the same on both columns.. if the 1st set of columns is empty, then just proceed with the next set of columns.. this is what i am trying to achieve..
 
Last edited:
Upvote 0
Instead of Key:=Range(rng)

Just us Key:= rng

Hope this will help :)
 
Upvote 0
Hi LeanneBG,

See new code below.

Notes on dates:

Regardless for format a date is stored as a number. Date and Time is stored as number with decimal places.

So it does not matter if you format before or after the sort. For your info :

15/03/2019 11:52:18 AM is actually stored by the system as 43539.49465


the whole no represents the no of days since 01/01/1900 and after the decimal place is a % of 24 hours ie (.25 = 6am, .5 = Noon, .75 = 6pm and so on)

Enjoy your code.

Sub Sort()
Dim lngMaxRow As Long

Sheets("SRPT").Select
lngMaxRow = Sheets("SRPT").Range("I1048576").End(xlUp).Row
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:I" & lngMaxRow), 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
Sheets("SRPT").Range("I2:I" & lngMaxRow).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"



End Sub
 
Upvote 0
Hi James and Brombrough
Thanks for your help! I was able to combine both of your codes and was able to form below. Not sure if there's a better way to formulate since it's a bit long but it gets the job done so thank you both! :)
However, the highlighted code below on the date formatter doesn't work. After running end to end, the format is still like this 2019-03-15 13:07:56 instead of this 15/03/2019 12:52:33 PM. What is weird is that if i select the last number and click enter, it changes into the right format 15/03/2019 12:52:33 PM. I checked my calculation options and it is automatic already so not sure how to fix this one.. Hope you can help again.. Thank you!
Code: 
Sub Sort()
Dim rng As Range, cell As Range, LastRowInR As Long, LastRowInS As Long, rngS As Range




Sheets("SRPT").Select
ActiveSheet.AutoFilterMode = False


LastRowInI = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Set rng = ThisWorkbook.Sheets("SRPT").Range("I2:I" & LastRowInI)


Sheets("SRPT").Range("I2:I" & LastRowInI).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"


    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Add Key:=rng, 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
   
''For the Next set of data or column


ActiveSheet.AutoFilterMode = False


LastRowInS = ActiveSheet.Cells(Application.Rows.Count, "M").End(xlUp).Row
Set rngS = ThisWorkbook.Sheets("SRPT").Range("S2:S" & LastRowInS)




[U][B]Sheets("SRPT").Range("S2:S" & LastRowInS).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"[/B][/U]


    Range("M1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Add Key:=rngS, 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
 
Upvote 0
Brombrough said:
Hi LeanneBG,

See new code below.

Notes on dates:

Regardless for format a date is stored as a number. Date and Time is stored as number with decimal places.

So it does not matter if you format before or after the sort. For your info :

15/03/2019 11:52:18 AM is actually stored by the system as 43539.49465


the whole no represents the no of days since 01/01/1900 and after the decimal place is a % of 24 hours ie (.25 = 6am, .5 = Noon, .75 = 6pm and so on)

Enjoy your code.

Sub Sort()
Dim lngMaxRow As Long

Sheets("SRPT").Select
lngMaxRow = Sheets("SRPT").Range("I1048576").End(xlUp).Row
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:I" & lngMaxRow), 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
Sheets("SRPT").Range("I2:I" & lngMaxRow).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"



End Sub
Click to expand...

Hi Brombrough
Thanks for your help! I was able to combine both of your codes and was able to form below. Not sure if there's a better way to formulate since it's a bit long but it gets the job done so thank you both! :)
However, the highlighted code below on the date formatter doesn't work. After running end to end, the format is still like this 2019-03-15 13:07:56 instead of this 15/03/2019 12:52:33 PM. What is weird is that if i select the last number and click enter, it changes into the right format 15/03/2019 12:52:33 PM. I checked my calculation options and it is automatic already so not sure how to fix this one.. Hope you can help again.. Thank you

Code: 
[/COLOR]Sub Sort()
Dim rng As Range, cell As Range, LastRowInR As Long, LastRowInS As Long, rngS As Range




Sheets("SRPT").Select
ActiveSheet.AutoFilterMode = False


LastRowInI = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
Set rng = ThisWorkbook.Sheets("SRPT").Range("I2:I" & LastRowInI)


Sheets("SRPT").Range("I2:I" & LastRowInI).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"


    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Add Key:=rng, 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
   
''For the Next set of data or column


ActiveSheet.AutoFilterMode = False


LastRowInS = ActiveSheet.Cells(Application.Rows.Count, "M").End(xlUp).Row
Set rngS = ThisWorkbook.Sheets("SRPT").Range("S2:S" & LastRowInS)




[U][B]Sheets("SRPT").Range("S2:S" & LastRowInS).NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"[/B][/U]


    Range("M1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("SRPT").AutoFilter.Sort.SortFields.Add Key:=rngS, 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[COLOR=#574123]
 
Upvote 0
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?
 
Upvote 0
Brombrough said:
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?
Click to expand...

Hi Brombrough - it is formatted as general.. but as checked, it is aligned to the left..
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
198
Coco1
C
C
  • Question
Daily Run data (copy data in loop by daily in worksheet)
Replies
3
Views
148
dermie_72
D
C
  • Solved
How to not set a range in VBA
Replies
7
Views
382
Coco1
C
M
  • Question
Help with my code
Replies
9
Views
386
kevin9999
K
V
  • Solved
Autofilter error
Replies
3
Views
200
Alex Blakenburg
A

Forum statistics

Threads
1,214,614
Messages
6,120,530
Members
448,969
Latest member
mirek8991

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top