Sort Date by last date in clumn A

[adamsm]

Hi anyone,

I'm trying to figure out a way how to make the following code to sort date in column A of the active sheet by the last date on bottom row and the first date on the top row. Meaning in an ascending manner.

Sub SortDate()
    Dim c As Integer
    With ActiveSheet.Range("A1").CurrentRegion
        c = .Find(What:="Date", After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Column
            .Sort Key1:=.Cells(1, c), Order1:=xlDescending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub

My column headers are on row 4. And I have data columns from column A to column I.

At present the code sorts date by the last date on top and first date on bottom. Meaning in an descending manner.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[melewie]

Try this

Sub SortDate()
    Dim c As Integer
    With ActiveSheet.Range("A1").CurrentRegion
        c = .Find(What:="Date", After:=.Cells(1, 1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Column
            .Sort Key1:=.Cells(1, c), Order1:=xl[COLOR=red]A[/COLOR][COLOR=red]scending[/COLOR], Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
End Sub

 

[adamsm]

Thanks for the help. If my column headers are in row 4 how should I change the code accordingly. Any help would be kindly appreciated.

 

[melewie]

is the data always in the same range i.e A4:I31 or is this like likey to change. if so will it always have the same amount of rows but variable columns?

 

[adamsm]

The data range always get changed. Meaning it always does get added from 5 and onwards. But the data always stays between column A and I.

If so how should I change the code accordingly. Any help on this would be kindly appreciated.

 

[melewie]

Sorry for the late reply, this is untested but should work

Sub sortbydate()
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
End Sub

Not the 'proper' way to do it but with out having a play best I can come up with

 

[adamsm]

Sorry for being late to response. Any ways thanks for the help. I do really appreciate your help.