VBA for Custom Autosort

Originally Posted by Leith Ross

Hello Pierce Quality,

Can we assume the date used for the sort will always be today's date?

Hello Leith!

The date will not be the current date, but will always be grouped as the same date, entered by the user, as a value in the Date column.

Thanks!

Originally Posted by Leith Ross

Hello Pierce Quality,

Will the user enter the new date group below the old data or can the data be inserted anywhere the user likes?

Hello Leith,

It will be inserted below the data as time progresses, always ascending.
Thanks!

Originally Posted by Leith Ross

Hello Pierce Quality,

So, the macro also needs to save the last row number when the workbook is saved to know where the new data will start next time, yes?

Hello Leith,

Yes, the sheet will be updated my multiple users several times a day.
Thanks!

Originally Posted by Leith Ross

Hello Pierce Quality,

Okay, I think this should work for you. The macro will create a log file, in the same directory as the workbook, that contains the last row on the worksheet "BeforeData" whenever the users does a Save or SaveAs. The range to be sorted is defined from the old last row + 1 to the current last row. The data is sorted first by Date then by Area.

Hello Leith,

I'm afraid that direction wont quite work for our situation with contiginous data, but is very close. In speaking with some of the users, we wont need to last row data to be saved anymore and we are going to run it as a macro instead of a before save event on a single book to pilot the change.

Here's what I ended up coming up with:

Please Login or Register  to view this content.

This seems to work well for a basic application so far, I also set the end selection to be on the first open cell in column A after the data, (you would need to add a header in A1 in the example book for this to work).
Do you have any insights on how I could make this code better? Eventually this will be sorting tens of thousands of rows of data so I would like to work out the kinks as early as I can.

Thanks!

Hi Pierce Quality,
maybe so

Please Login or Register  to view this content.

Originally Posted by nilem

Hi Pierce Quality,
maybe so

Please Login or Register  to view this content.

Hello nilem,

I ran the code as this

Please Login or Register  to view this content.

on about 8000 rows of data vs the other auto sort on the same amount of data and it appears that yours is slightly faster, so I think it will be the way to go. Is there a faster way to select the first open cell in column A after the data?
Thanks!

Hi Pierce Quality,
try it (not faster, it just prettier )

Please Login or Register  to view this content.

Originally Posted by nilem

Hi Pierce Quality,
try it (not faster, it just prettier )

Please Login or Register  to view this content.

Thanks nilem, prettier is better for sure!

Ill be closing this thread as solved now, special thanks to Leith Ross and nimlem for all the help!

Thanks again!