vba sort by date

Pepe.VBA · May 24th 2016

Hi Everyone,
On my spreadsheet, E2:E1215 has dates , my vba (produced from recorded macro. its not providing the correct result.
I have searched various posts to do it with vba and all are for very advanced applications of the sort.
Can someone please assist with the basic vba for this

Code

Sub Sort()
    ActiveWorkbook.Worksheets("Updates").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Updates").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E2:E1215"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Updates").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Display More

Pepe.VBA · May 24th 2016

Re: vba sort by date

I should have stated in the post that the aim is to do a sort by date ascending (oldest to newest) over cell range ("E2:E1215")

Caleeco · May 31st 2016

Re: vba sort by date

Hello Pepe,

When you say, it is not working correctly. What is the problem? is it throwing up any error messages? is it not sorting dates sequentially?

The code below is a structure you can copy. It will sort on column E (dates).

However, it assumes that there is data ajacent to the Dates column E. So the range I have set is for columns C:E (change this as you please). The other thing to check would be that the cells are all formatted as dates and not text.

Code

Sub SortData()
Dim rData As Range
Dim rDates As Range
Dim lr As Long
With ActiveWorkbook.Worksheets("Updates")
    .Activate
    .ShowAllData
    lr = .Cells(Rows.Count, "E").End(xlUp).Row
    Set rData = .Range("C2", Range("E" & lr))
    Set rDates = .Range("E2", Range("E" & lr))
End With
rData.Sort key1:=rDates, order1:=xlAscending, Header:=xlNo
End Sub

Display More

Let me know how you get on.

Many Thanks
Caleeco

vba sort by date

Participate now!

Share