Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Can I configure (possibly with scripts) Excel to automatically resort a table every time an entry is updated?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

A quick google gave me that:

http://www.techsupportforum.com/microsoft-support/microsoft-office-support/252123-can-someone-help-me-auto-sort-excel.html#post1499027

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets(1).Range("E9:F32"), Target) Is Nothing) Then
        DoSort
    End If
End Sub

Private Sub DoSort()
    Worksheets(3).Range("E3:M7").Sort Key1:=Worksheets(3).Range("M3"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L3"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E10:M14").Sort Key1:=Worksheets(3).Range("M10"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L10"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E17:M21").Sort Key1:=Worksheets(3).Range("M17"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L17"), Order2:=xlDescending, Header:=xlYes
    Worksheets(3).Range("E24:M28").Sort Key1:=Worksheets(3).Range("M24"), Order1:=xlDescending, _
        Key2:=Worksheets(3).Range("L24"), Order2:=xlDescending, Header:=xlYes
End Sub

I don't have much knowledge of VBA, but I'll try to explain how to customize this, if I fail with that, let me know. ;)

With the first sub you define when the function is fired. The line above says start DoSort when anything on worksheet 1 in cells E9 to F32 is changed.

DoSort sorts some ranges, and can be adapted with your own data ranges, too. Simply delete the uneeded lines and change worksheet numbers and data ranges.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.