Automatically sort table based on a column when data is updated in that column (VBA)

[quantmaven]

Hi,
I have the table in the picture below. The cells D2:D26 are daily returns for stocks that are updated frequently (every 5 seconds). I want to sort the whole table A1:I26 based on cells from D2:D26 descending whenever the numbers inside cell D2:D26 are updated. I tried codes but nothing successful so far. Can any of you Excel wizards help me out? Thanks!

 

[quantmaven]

Auto-sorting column of a table help!

I want to auto-sort a table from B3:J28 with header on first row based on the column E3:E28 as data from that column changes. Can someone explain me why the code below doesn't work?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("E3:E28")) Is Nothing Then
Range("E3:E28").Sort Key1:=Range("E3"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Thanks!

 

[Joe4]

Re: Auto-sorting column of a table help!

I have combined your two threads together, since they seem to be talking about the same problem. For future reference, all clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will typically be locked or deleted (rule 12 here: Forum Rules).

Regarding your question, you said that you want to sort the range B3:J28, yet the sort range in your code is E3:E28.

Range("[COLOR=#ff0000]E3:E28[/COLOR]").Sort Key1:=Range("E3"), _

You need to change that. The Key tells it which field to sort by, but the Sort range needs to be the entire range you want to sort.
Otherwise, you are just sorting column E and not any of the other columns along with it.

Also, how are the values in column E changing?
Worksheet_Change will only pick up changes made manually (direct entry or copy/paste).

 

[quantmaven]

Re: Auto-sorting column of a table help!

Also, how are the values in column E changing?
Worksheet_Change will only pick up changes made manually (direct entry or copy/paste).

1. The values are changing from an Excel API that feeds the data in those cells. They are streaming stock quotes. What should I be using instead?

2. It seems to be sorting correctly except whichever cell gets updated is isolated from the sort, is there a reason it is behaving like this? (See pic below)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("E3:E28")) Is Nothing Then
Range("B3:J28").Sort Key1:=Range("E3"), _
Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub

Thanks again!

 

[Joe4]

Re: Auto-sorting column of a table help!

I am not sure what you mean. It seems to sort fine for me, but I do not have an Excel API, I am just manually updating the cells.

I am not familiar with how that API works, but suspect it may not be triggering the "Worksheet_Change" event procedure.
You may need to use something like a "Worksheet_Calculate" event procedure instead, which works a bit differently (there is no "Target" cell in that).
It would just look something like:

Private Sub Worksheet_Calculate()
    Range("B3:J28").Sort Key1:=Range("E3"), _
        Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
End Sub

 

[quantmaven]

Re: Auto-sorting column of a table help!

It would just look something like:

Private Sub Worksheet_Calculate()
    Range("B3:J28").Sort Key1:=Range("E3"), _
        Order1:=xlDescending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
End Sub

Seems to work but it crashes Excel. I think I'm going to give up and just create another table with a rank on first column that will fetch the data from that table. It is probably not the fanciest solution but at least it is working.

Thanks

 

[Joe4]

Re: Auto-sorting column of a table help!

Seems to work but it crashes Excel.

The issue is that if it your API is constantly making those changes, that event procedure is constantly being called, so that may overload things and cause the crash.
It is probably not the best idea to have event procedure code running on a workbook that is going through constant updates every few seconds.
Yes, I think the best idea would be to create a macro to copy the data at that time, and sort that static copy instead of your data constantly being updated.