Auto-sort multiple columns based on change to one column

gli

Board Regular
Joined
Jun 8, 2006
Messages
95
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!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
Hi.
Try:
VBA Code:
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
 
Solution

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
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

Board Regular
Joined
Jun 8, 2006
Messages
95
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top