SOLVED

Auto sort in Excel based on multiple columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2459860%22%20slang%3D%22en-US%22%3EAuto%20sort%20in%20Excel%20based%20on%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2459860%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20seen%20multiple%20solutions%20for%20how%20to%20use%20VBA%20codes%20to%20auto-sort%20an%20excel%20sheet%20when%20there's%20a%20change%20in%20data.%20All%20these%20solutions%2C%20however%2C%20are%20based%20on%20sorting%20by%20values%20of%20a%20SINGLE%20column.%3C%2FP%3E%3CP%3ELooking%20at%20my%20attached%20sample%2C%20is%20there%20any%20way%20to%20have%20the%20data%20updated%20automatically%20by%20sorting%20values%20by%20looking%20at%20Column%20B%20first%2C%20and%20then%20column%20C%3F%20(descending%20would%20be%20preferred).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2459860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2459954%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20sort%20in%20Excel%20based%20on%20multiple%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2459954%22%20slang%3D%22en-US%22%3EHere's%20the%20VBA%20code%20I%20could%20put%20together%20that%20works%3A%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EIf%20Not%20Intersect(Target%2C%20Range(%22B3%3AC500%22))%20Is%20Nothing%20Then%3CBR%20%2F%3EColumns(%22A%3AF%22).Sort%20Key1%3A%3DRange(%22B4%22)%2C%20Key2%3A%3DRange(%22C4%22)%2C%20_%3CBR%20%2F%3EOrder1%3A%3DxlDescending%2C%20Order2%3A%3DxlDescending%2C%20Header%3A%3DxlYes%2C%20_%3CBR%20%2F%3EOrderCustom%3A%3D1%2C%20MatchCase%3A%3DFalse%2C%20_%3CBR%20%2F%3EOrientation%3A%3DxlTopToBottom%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I've seen multiple solutions for how to use VBA codes to auto-sort an excel sheet when there's a change in data. All these solutions, however, are based on sorting by values of a SINGLE column.

Looking at my attached sample, is there any way to have the data updated automatically by sorting values by looking at Column B first, and then column C? (descending would be preferred).

 

Many thanks in advance!

 

1 Reply
best response confirmed by Stevy_M (New Contributor)
Solution
Here's the VBA code I could put together that works:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B3:C500")) Is Nothing Then
Columns("A:F").Sort Key1:=Range("B4"), Key2:=Range("C4"), _
Order1:=xlDescending, Order2:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub