Automatically Sort Rows in Excel by Date

Question

I'm currently trying to self-teach myself VBA code in Excel, but I've run into a problem.

What I'm wanting Excel to do is to automatically order specific rows according to the date entered in specific cells. For example, dates will be entered into cells E36-E40 only, and as they are entered rows 36-40 (not including column A) will automatically sort themselves according to the oldest date first.

I've done a macro recording of this and it has spat out this code:

Sub AutoSort()

Range("B36:H40").Select
ActiveWorkbook.Worksheets("SHEET NAME").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SHEET NAME").Sort.SortFields.Add Key:=Range( _
    "E37:E40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("SHEET NAME").Sort
    .SetRange Range("B36:H40")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

I've tried to make this automatic as shown below, however does not work!

Sub Worksheet_Change1(ByVal Target As Range)
If Intersect(Target, Range("E36, E37, E38, E39, E40")) Is Nothing Then
Exit Sub
Else
Sub AutoSort()

Range("B36:H40").Select
ActiveWorkbook.Worksheets("SHEET NAME").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("SHEET NAME").Sort.SortFields.Add Key:=Range( _
    "E37:E40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveWorkbook.Worksheets("SHEET NAME").Sort
    .SetRange Range("B36:H40")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End If
End Sub
End Sub

Any help would be greatly appreciated!

score 1 · Accepted Answer · 2016-11-17 05:54:58Z

1

MSDN definition of Me: Provides a way to refer to the specific instance of a class or structure in which the code is currently executing.

I used Me instead of ActiveWorkbook.Worksheets("SHEET NAME") because this code is only relevant to the worksheet that calls the event. I originally used ActiveSheet but if a Macro changed the values from a different worksheet than that worksheet would be active and it would be sorted.

Turn off EnableEvents, whenever changing values on the ActiveSheet from the Worksheet_Change event. This will prevent the Worksheet_Change event from triggering itself causing an infinite loop and crashing Excel.
Include an Error Handler that will turn the events back on, if an error is thrown.
The key range started at row 37
.Header = xlYes should be .Header = xlNo

Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ResumeEvents
    If Not Intersect(Target, Range("E36:E40")) Is Nothing Then
        With Me
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=Range("E36:E40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With .Sort
                .SetRange Range("B36:H40")
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With

    End If
ResumeEvents:
    Application.EnableEvents = True
End Sub

edited Nov 17, 2016 at 5:54

answered Nov 17, 2016 at 5:35

user6432984

Sign up to request clarification or add additional context in comments.

3 Comments

Shai Rado Over a year ago

Thomas, isn't Range("E36, E37, E38, E39, E40") equivalent to Range("E36:E40") ?

Shai Rado Over a year ago

maybe it's worth while, explaining in your answer, that this code belongs in the Worksheet_Change event of "SHEET NAME" worksheet

user6432984 Over a year ago

@ShaiRado Thanks again. I replaced ActiveSheet with Me, in case, the values are changed from a Macro with a different ActiveSheet.

user3598756 · Accepted Answer · 2016-11-17 07:39:54Z

0

using Sort() method of Range leads to a more concise code:

Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrHandler
    If Not Intersect(Target, Range("E36:E40")) Is Nothing Then _
        Range("B36:H40").Sort key1:=Range("E36"), order1:=xlAscending, Header:=xlNo, SortMethod:=xlPinYin, DataOption1:=xlSortNormal, MatchCase:=False, Orientation:=xlTopToBottom

ErrHandler:
    Application.EnableEvents = True
End Sub

or, encapsulating the sorting operation into a specific sub:

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E36:E40")) Is Nothing Then AutoSort Range("B36:H40"), Range("E36")
End Sub


Sub AutoSort(dataRng As Range, orderCol As Range)
    Application.EnableEvents = False
    On Error GoTo ErrHandler
    dataRng.Sort key1:=orderCol, order1:=xlAscending, Header:=xlNo, SortMethod:=xlPinYin, DataOption1:=xlSortNormal, MatchCase:=False, Orientation:=xlTopToBottom

ErrHandler:
    Application.EnableEvents = True
End Sub

answered Nov 17, 2016 at 7:39

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

2 Comments

SammMmm Over a year ago

Thanks for the reply, I've tried it and it works great. I do have another question though, is it possible to add a function that will auto sort the same rows according to the time entered in F36:F40?

user3598756 Over a year ago

Sort() method of Range object lets you sort on up to three keys. If you need more then you must use the Sort() method of Worksheet object

bzimor · Accepted Answer · 2016-11-17 04:53:06Z

0

Don't encapsulate your Subprocedure AutoSort() in your other procedure. Put your AutoSort() procedure in module, then call it within worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E36, E37, E38, E39, E40")) Is Nothing Then
   Exit Sub
Else
   AutoSort
End If
End Sub

Also, change .Header = xlYes to .Header = xlNo if Row 36 doesn't contain header.

answered Nov 17, 2016 at 4:53

bzimor

1,6262 gold badges16 silver badges26 bronze badges

Collectives™ on Stack Overflow

Automatically Sort Rows in Excel by Date

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related