Macro to Sort Dates in Column Q

Grepees

Board Regular
Joined
Apr 15, 2009
Messages
71
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Bit of a guess without knowing what else is on the sheet and where it is, try this with a copy of your workbook.

VBA Code:
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’)
 
Upvote 0
Bit of a guess without knowing what else is on the sheet and where it is, try this with a copy of your workbook.

VBA Code:
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
 
Upvote 0
With dates are from Q2 going down all the way
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
  • 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.

1675048282472.png
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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