Excel 2003 Auto Sort

Hi there. I'm not especially clued up on using Excel, but I'm trying to find a way of making it sort the data on a sheet automatically every time I enter data in one particular column.

Basically, I have a rapidly growing sheet of data which has 13 columns. The last column (column M) always contains a date.

Each day when I open the spreadsheet, I sort the whole sheet (at the moment 250 rows, although the top 2 rows are column headers, so I sort it from row 3 onwards) by date (i.e. Column M) so that the newest dates are at the top, at the oldest at the bottom.

I realise that having to manually sort it each time isn't a major hassle, but wanted to see if there was a way of making Excel automatically do this sort every time a new date was entered (i.e. a cell in Column M was altered), so that as soon as I enter a new date in a cell and hit return, it immediately drops down the list (or up I guess, depending on what date has been changed).

From what I read, it appears that this can be done by using a macro, but to be blunt, I have no real idea what a macro is, and therefore have no clue how to create one.

Can anyone help? If anyone is potentially kind enough to post a macro that would do this and provide a quick 'idiots guide' as to how to actually insert it, that would be hugely appreciated.

Cheers, Matt.

Maybe:

Please Login or Register  to view this content.

For 2003 Tools>Macro>VBA Editor-select the sheet on the right side> on the left side drop down Choose Worksheet, Right side drop down Choose Change. Paste the code there.

BTW, Welcome to the Forum.

Yes, depending on how you enter your data, you can have a macro that does this tied to a key stroke (control + some letter of your choosing), or you can do this in the worksheet_change event code automatically so that the code is run as soon as something is entered into that date column.

Oh, looks like John H Davis beat me to it. Thanks John.

Originally Posted by bmxfreedom

Oh, looks like John H Davis beat me to it. Thanks John.

You're welcome. I hope it is what jackson_mj wants.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

For 2003 Tools>Macro>VBA Editor-select the sheet on the right side> on the left side drop down Choose Worksheet, Right side drop down Choose Change. Paste the code there.

BTW, Welcome to the Forum.

Wow, I didn't expect such a quick response!! Thank you very much.

As grateful as I am, it doesn't quite do what I was after. Having put that code in, when I alter a cell in Column M, it automatically sorts it, but with 2 slight issues:

1. When it sorts Column M, the rest of the data (in Columns A-L) doesn't move with it. This is crucial as the rest of the data in each row is specifically related to the date in Column M, so if Column M sorts but the rest of the data doesn't move with it, my data becomes jumbled and unusable.
2. It has sorted it so that the oldest dates are at the top and the newest at the bottom - is it possible to have the newest at the top and oldest at the bottom?

I really appreciate the help John, thank you very very much

Maybe:

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

That seems to do exactly what I was after!! Thank you!

When I alter a cell in Column M and hit return, it sorts it exactly as I was hoping for, but then all the data from row 2 downwards is highlighted for some reason. I minor gripe I realise, but is there any way from stopping it from doing this, i.e. maybe it could just select the next cell down or something? It just makes it feel a bit odd to use when it highlights nearly the whole sheet each time.

Thanks

Not sure if this change will work.

Please Login or Register  to view this content.

Duplicate post in error.

Originally Posted by JOHN H. DAVIS

Not sure if this change will work.

Please Login or Register  to view this content.

Hmm no that's behaved oddly - it still highlights all the data, and also removed all my formatting of cells in all the data fields + row 2 (which is a secondary header row).

Can you attach a sample file which errors?

Originally Posted by JOHN H. DAVIS

Can you attach a sample file which errors?

Of course. The file I've attached is just purely for sample purposes, not real data, and I've also removed any code so you can see what happens for yourself.

Thank you

Test File.xlsx

Maybe:

Please Login or Register  to view this content.

I didn't know you have two rows in your header.

I think what jackson means by highlight might be selected cells... Jackson, add the code:

Please Login or Register  to view this content.

To the end of your macro. This will cause the active cell to be selected instead of the sort entire range, which when selected appears "highlighted" to some extent.

Originally Posted by bmxfreedom

I think what jackson means by highlight might be selected cells... Jackson, add the code:

Please Login or Register  to view this content.

To the end of your macro. This will cause the active cell to be selected instead of the sort entire range, which when selected appears "highlighted" to some extent.

Ok I'll try it (the spreadsheet's on my work computer so can't try until the morning).

Do i put that line right after all other code?

Also, is there a code that would make it select the next cell down?

Thanks.

@bmxfreedom I noticed that too. Maybe your input will clear up the misunderstanding.

@jackson_mj The addition should be placed on the line before End Sub. Use the code in Post#14 which takes into account your two header rows.

Originally Posted by JOHN H. DAVIS

@bmxfreedom I noticed that too. Maybe your input will clear up the misunderstanding.

@jackson_mj The addition should be placed on the line before End Sub. Use the code in Post#14 which takes into account your two header rows.

Ok brilliant, i'll try it tomorrow at work.

I really really appreciate your guys help, thank you so much!!

Hi guys. So I added the code to the spreadsheet this morning, which (as long as I've understood everything correctly) is now as follows:

Please Login or Register  to view this content.

I have attached a Test File that has this code added.

As you will see, it sorts the data automatically as I want, apart from the fact that it doesn't sort Row 3.

When it has done this automatic sort, the cell that now becomes selected (not highlighted, apologies for the confusing terminology) is A3, which is obviously the first cell that has data in (i.e. because rows 1 & 2 are headers).

I'm hoping to be able to have it so that it does this automatic sort and includes all the rows with data (so row 3 downwards), and if I have just changed a date in, for examples sake, cell M175, when I hit return and it does this automatic sort, the selected cell becomes M176 (i.e. just goes to the next cell down as would normally happen in Excel), regardless of how the data has moved in the automatic sort that will have just occurred. That way if I'm working on a group of cells in one area, if I change a date in one of them which sends it, for example, way down the list to row 300 when I hit return and it does the auto sort, I don't then have to manually scroll back up to try and find where I was. Does that make sense?

Would you be able to have a look at it for me? I know I keep saying it, but I really am so appreciative of the help

Test File + Macro.xlsm

For anyone that is interested, I have actually now sorted this.

Another kind chap who was helping me with this provided the following code:

Please Login or Register  to view this content.

It seems to work perfectly. As mentioned, I personally have no knowledge of Macros etc, but just thought I'd post it here incase it was of use to anyone, and to say thanks for your help anyway.

Cheers