Excel 2003 Auto Sort

[jackson_mj]

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.

[JOHN H. DAVIS]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1)) Is Nothing Then
        Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1).Select
        Selection.Sort Key1:=Range("M2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
End Sub

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.

[jackson_mj]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1)) Is Nothing Then
        Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1).Select
        Selection.Sort Key1:=Range("M2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
End Sub

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

[JOHN H. DAVIS]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1)) Is Nothing Then
        Range("A2:M" & ActiveSheet.UsedRange.Rows.count + 1).Select
        Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
End Sub

[jackson_mj]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1)) Is Nothing Then
        Range("A2:M" & ActiveSheet.UsedRange.Rows.count + 1).Select
        Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
End Sub

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

[JOHN H. DAVIS]

Not sure if this change will work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M2:M" & ActiveSheet.UsedRange.Rows.count + 1)) Is Nothing Then
        Range("A2:M" & ActiveSheet.UsedRange.Rows.count + 1).Select
        Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
Range("A2:M" & ActiveSheet.UsedRange.Rows.count + 1).interior.colorindex = xlnone
End Sub

[JOHN H. DAVIS]

Duplicate post in error.

[bmxfreedom]

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.

[bmxfreedom]

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

[JOHN H. DAVIS]

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

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

[bmxfreedom]

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

ActiveCell.Select

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.

[jackson_mj]

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

ActiveCell.Select

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.

[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.

[jackson_mj]

@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!!

[jackson_mj]

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:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M3:M" & ActiveSheet.UsedRange.Rows.Count + 1)) Is Nothing Then
        Range("A3:M" & ActiveSheet.UsedRange.Rows.Count + 1).Select
        Selection.Sort Key1:=Range("M3"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End If
ActiveCell.Select
End Sub

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

[jackson_mj]

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long, lCol As Long
If Intersect(Target, Range("M:M")) Is Nothing Then Exit Sub
If Target.Row < 3 Then Exit Sub
If Not IsDate(Target.Value) Then Exit Sub
With ActiveSheet
  lRow = .Range("M" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
  lCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
  With .Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("M3:M" & lRow), SortOn:=xlSortOnValues, _
      Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A2:" & Cells(lRow, lCol).Address)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  ActiveCell.Offset(0, 0).Select
End With
End Sub

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