Excel Macro Help Pls - Date order

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
136
Hi again

I would very much appreciate further help regarding a macro, please

In a previous post, https://www.mrexcel.com/forum/excel...ange-sheets-jan-dec-whichever-actve-time.html Osvaldo Palpeiro helped me with a macro which would put rows in date order, based on the day of the month recorded in 'B' column. It seemed to work well BUT, now, due to more activity, the days of the month include double figure numbers, ie '23', '30' etc so the 'Sort Date' is put in the older computer order, ie 1-9, then 10-19, then 20-19, 30-31, which isn't the Date Order I had anticipated.

Can someone please tell me how I can achieve 1-31 (ie 1-9, 10-19, 20-29, 30-31) order, as I had expected, please?

My code is as follows:

Code:
Sub Date_Order_1()
  '
  ' Date_Order Macro
  ' Puts Date (Column B) in ascending order
  '
  ' Keyboard Shortcut:
     
     With ActiveSheet
       .Unprotect
       .[A16:BZ115].Sort Key1:=[B16], Order1:=xlAscending
       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     End With

  End Sub

Many thanks for any help with this :)
%
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
As long as the dates are actually dates and not text that looks like dates, the code you have will work fine. They should align right if you restore Excel's default horizontal alignment, which is General and widen the column enough to see.

If not, select the column, Data > Text to Columns, Finish.
 
Upvote 0
Hi shg

PERFECT !!! Wonderful, thank you very much. I had to do the Data, Text to Columns etc and then tried again and IT'S WORKED :)

Thank you so very much. I can get on with this now, hopefully ... until the next problem ;)

So very grateful. Brilliant forum :)
 
Upvote 0
Hi, I need more help with this macro, please!

I've just discovered that, when using this Macro to 'Sort Date', ie to put date (number) in ascending order, it seems the whole row does not always go with the date, Sponsor etc. As a result, the amounts paid in the bank account and/or data along the row, does not alway tally with the name at the beginning of the row. Therefore, this isn't working as I had hoped.

Some of the rows appear to be correct whereas others seem to have random data; some rows even seem to have disappeared altogether

The code I'm using is in my original post, as above.

Maybe someone can help solve this issue, please?

I appreciate any help and advice offered. Many thanks

Here is a 'mock-up' of the spreadsheet (in principle) which I hope helps appreciate the situation

http://
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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