Macro to Sort Dates

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
142
Hi, I need more help with this macro, please!

When I run ths Macro (many thanks to Osvaldo Palpeiro and shg for their help with this so far)

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

Unfortunately, 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, do not alway tally with the name at the beginning of the row.

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

I'm hoping 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


Example-Sheet-1.jpg
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Is the range, [A16:BZ115], correct? The example you gave doesn't fit this range.
 
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Hi yky

The example given is just that, a 'mock up'. My apologies for not pointing that out. I just used the jpg again, after using it in another post and didn't think to mention the ranges etc.

The ranges for this ''mock up' would be different.

I've checked the range(s) I'm working on, including hidden, pre-formatted, rows and all seem to tally. I write in plural there, as there are 4 ranges, 4 Macros (ranges adjusted) and 4 buttons to activate each Macro

I'll post a section of the 'real' spreadsheet shortly (by editting this post, if it's possible)

Many thanks for interest and, hopefully, help :)
 
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Here is a section of the 'real' spreadsheet Names have been changed, for obvious reasons. Hopefully it's more helpful than the mock-up
:)

For-jpg.jpg
[/URL]free picture uploading[/IMG]
 
Last edited:
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Would you also post sorted result and highlight irregularities?
 
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Would you also post sorted result and highlight irregularities?
This will be more difficult. I've deleted the real names and put in 'random' names to protect identity. I could do it again, with 'before' and 'after' but to cross-check will take me a little longer, obviously, so it could be a few days before I can post anything of this nature, due to other things I need to do.

I'll see what I can do ...

J
 
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

Hi

Here are both 'Before' and 'After' re sorting. The ones in RED are the ones that are wrong. Either the DATE, Sponsored, Gross, GA vs No GA are wrong. On the 'Before' sheet, there are three blank rows which are removed on the 'After' sheet, which is good.

The 'names' might be slightly incorrect re tally but, even so, the 'After' sheet is not correct.

I have set the 'Date' column as (Format) General and then, as previously instructed, set the column via Data > Text to Columns. Should the remaining columns also be set to 'Text to Columns' perhaps?

Hopefully, someone can correct this for me.

Before-After.jpg
[/URL][/IMG]
 
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

What's wrong with row 23 of the after sheet? I assume it is also the row 23 of the before sheet.
 
Last edited:
Upvote 0
Re: VBA Help, pls - Macro to Sort Dates still not working as hoped

My mistake - I missed off the Date & Sponsor columns on the image of the 'After' sheet.

On row 23, on the 'Before' sheet, the Sponsor is 'MH' whereas on the 'After' sheet, the initials are ML

Some of the dates don't match the original dates for the Sponsors, some of the Sponsors names are missed off or changed to another name, etc. All these things seem quite random.

I've tried 'Sort Date' on another tab (less data) with smaller number of rows/columns, ie row 16 - 28; columns A - R - having copied into a new Book, then deleting columns K-P, and all seems OK but when I attempt to sort on the original sheet, it's all random mistakes again.

Many thanks for any help

Here's a better image
2-Before-After.jpg
[/URL][/IMG]
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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