Forum Discussion

webbsinferno's avatar
webbsinferno
Copper Contributor
Aug 20, 2020

Auto Sort When Data Changes

Hello, 

 

I'm building an excel spreadsheet to track my investments and I have the spreadsheet ordered based on overall % of my portfolio, where the largest position is at the top and smallest at the bottom. My goal is to have it automatically sort (from largest to smallest) when data is updated along with the other corresponding rows. I also don't want any of the formulas in the corresponding cells to get messed up. I can go to the Sort & Filter option on the home ribbon but I don't want to have to do that manually every time the data changes. Thanks in advance. This seems like it should be easier than it is. 

excel
Formulas and Functions
Macros and VBA

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Aug 20, 2020

    webbsinferno 

    It would be very helpful if a file always attached to the questions asked (without sensitive data), so that you can reach your goal faster and we will answer faster. a win-win situation. So you have to guess ... personally I was never good at that.

     

    I will still try to help 🙂

    Here is a macro as an example.

    Private Sub Worksheet_Change(ByVal Target As Range)
Range("A2:I32").Sort Key1:=Range("D8:D32"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

     

    I would be happy to find out if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Aug 20, 2020
      Sorry previous VBA macro is for date cells. For this / with cell range selection

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("A5:A300")) Is Nothing Then
      Range("A5:G300").Sort Key1:=Range("A5"), _
      Order1:=xlAscending, _
      Header:=xlNo
      End If
      End Sub

Resources