Macro to Sort Dates in Column Q

[Grepees]

Good morning Good people,

I have data in an excel sheet that keeps changing with time. In column Q is a list of dates with the field name "Contract End Date". I am in need of a macro that i can run that will sort out the date column in order of the most recent dates first. The sheet name is "Summary List". Any help would be most appreciated. Thanks.

Grepees

 

[Peter_SSs]

Bit of a guess without knowing what else is on the sheet and where it is, try this with a copy of your workbook.

Sub SortByDate()
  With Sheets("Summary List")
    With .Range("Q1").CurrentRegion
      .Sort Key1:=.Columns(17 - .Column + 1), Order1:=xlDescending, Header:=xlYes
    End With
  End With
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Grepees]

Bit of a guess without knowing what else is on the sheet and where it is, try this with a copy of your workbook.

Sub SortByDate()
  With Sheets("Summary List")
    With .Range("Q1").CurrentRegion
      .Sort Key1:=.Columns(17 - .Column + 1), Order1:=xlDescending, Header:=xlYes
    End With
  End With
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Noted. I will try and let you know. You started by saying "bit of a guess".......What else could complicate the smooth execution of this macro? Thanks.

Grepees

 

[bebo021999]

With dates are from Q2 going down all the way

Option Explicit
Sub sortR()
Sheets("Summary List").Activate
With Range("Q2:Q" & Cells(Rows.Count, "Q").End(xlUp).Row).EntireRow
    .Sort Range("Q1"), xlDescending
End With
End Sub

 

[Peter_SSs]

What else could complicate the smooth execution of this macro?

• Gaps in the data so CurrentRegion does not give the correct data to sort.
• Data that you want to sort is adjoining other data that should not be included in the sort.
• What you want to do with any rows that do have a date in column Q (if that is possible) and whether suck 'blanks' are actually blank or contain a formula returning ""
• etc

 

[Grepees]

• Gaps in the data so CurrentRegion does not give the correct data to sort.
• Data that you want to sort is adjoining other data that should not be included in the sort.
• What you want to do with any rows that do have a date in column Q (if that is possible) and whether suck 'blanks' are actually blank or contain a formula returning ""
• etc

This is exactly how the sheet looks like. I want it to sort based on column Q, which has the dates, with the earliest date first. Thanks.

 

[Peter_SSs]

Looks like one of the "etc" in my previous post should have been "Heading is not in row 1".

Then change Q1 to Q2 in my code and test that with a copy of your workbook.

Sub SortByDate()
  With Sheets("Summary List")
    With .Range("Q2").CurrentRegion
      .Sort Key1:=.Columns(17 - .Column + 1), Order1:=xlDescending, Header:=xlYes
    End With
  End With
End Sub