Auto Sort range by column value change

[ngen33r]

I have a sheet that I want to auto sort the cell range A13-T33 by column M descending when a cell in column M is updated.



I have tried:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range("M:M")) Is Nothing Then

Range("M13").Sort Key1:=Range("M33"), _

Order1:=xlAscending, Header:=xlYes, _

OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

End If

'End Sub</code>
I cannot get it to work.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...9-auto-sort-range-by-column-value-change.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("M:M")) Is Nothing Then
      Range("A13:T33").Sort Key1:=Range("M13"), _
         Order1:=xlAscending, Header:=xlYes, _
         OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom
   End If
End Sub

 

[ngen33r]

Sorry for the cross post, I wanted to find the most active forum to use regularly. The function does work but it will not update if the values in column M are calculated. I have cell M = J*K*L. Also can I change ascending to descending.

 

[Fluff]

How about

Private Sub Worksheet_Calculate()
Range("A13:T33").Sort Key1:=Range("M13"), _
   Order1:=xlDescending, Header:=xlYes, _
   OrderCustom:=1, MatchCase:=False, _
   Orientation:=xlTopToBottom

End Sub

 

[ngen33r]

This does update and arrange but after the update some kind of loop is running and laggs the sheet for about 30 iterations of something. The bottom left keeps blinking ready.

 

[Fluff]

Is this any better

Private Sub Worksheet_Calculate()
Application.Calculation = xlCalculationManual
Range("A13:T33").Sort Key1:=Range("M13"), _
   Order1:=xlDescending, Header:=xlYes, _
   OrderCustom:=1, MatchCase:=False, _
   Orientation:=xlTopToBottom
Application.Calculation = xlCalculationAutomatic
End Sub

 

[ngen33r]

It still goes through a bunch of iterations. Maybe what I am trying to do is not possible?

 

[Fluff]

Are columns J,K & L formulae or hard values?