Auto-sort multiple columns based on change to one column

[gli]

Hello All,

I am on Windows using Excel 365. I have a small spreadsheet that encompasses A3:E80. I have a header row in Row 3 and my data starts in Row 4. I would like VBA code to auto-sort this spreadsheet anytime a change is made to Column D (or specifically D4:D80). When it auto-sorts, I would like it to apply the following multiple sort order: Column D in descending order, Column A in ascending order, and Column B in ascending order. I have a macro that will sort the way I want it to but I can't seem to marry it with a worksheet change event. Been working on this for a little while now with no success. Any help on this would be appreciated!

 

[Osvaldo Palmeiro]

Hi.
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim LR As Long
  LR = Cells(Rows.Count, 1).End(3).Row
  If Intersect([D4:D89], Target) Is Nothing Then Exit Sub
    With ActiveSheet.Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("D4:D" & LR), SortOn:=xlSortOnValues, Order:=xlDescending
    .SortFields.Add Key:=Range("A4:A" & LR), SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add Key:=Range("B4:B" & LR), SortOn:=xlSortOnValues, Order:=xlAscending
    .SetRange Range("A4:E" & LR)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
   End With
End Sub

 

[mikerickson]

That code looks like it will do what you want, but automatic sorting makes typos difficult to notice or to correct. The user types something in a cell and the sheet automatically takes that typing somewhere else (i.e. sorts) before the user has a chance to see if they miskeyed or not. You might want the sort to be controled by a button rather than having it automatic.

 

[gli]

Hi there. It's late where I am and I will try the offered solution tomorrow. I will respond back about it at that point.

Mikeerickson, I am the only person who will be working with this file and the nature of what happens in column D is finite, so while I understand your concerns they will not be a factor here. I do appreciate the heads up though!

 

[gli]

Your code worked nicely, Osvaldo. Thank you very much!