Automatically rank list based on numbers from highest to lowest

[Legacy 386498]

Hi all,

I was wondering if it's possible in Excel to automatically rank numbers from highest to lowest? So in this example, you see that the numbers go from 5 to -1, but if I were to change the -1 to 6, Excel would automatically change the list so that cake is first, candy second and chocolate last. Is that possible?

Sure, I could add a line above, add column headers and filter them from highest to lowest each time the numbers change. But I was wondering, could Excel always update itself without me doing anything?

Thanks.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Candy[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Chocolate[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cake[/TD]
[TD]-1[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
Dim r As Range: Set r = Range("B2:B" & LR)
Dim tbl As Range: Set tbl = Range("A1:B" & LR)


If Not Intersect(r, Target) Is Nothing Then
    tbl.Sort Key1:=tbl.Cells(1, 2), Order1:=xlDescending, Header:=xlYes
End If


End Sub

 

[Legacy 386498]

Yes! This seems to work perfectly. Is there a way to control the range? Let's say from A2 to B9? And if I add or delete rows, I need to modify the code each time or is it possible to have a dynamic range?

Thanks.

 

[lrobbo314]

The range is set up to be dynamic. If you want it to begin in A2 you would write it like below, but it's already setting the number of rows dynamically.

Dim tbl As Range: Set tbl = Range("A2:B" & LR)

Adding and deleting rows shouldn't have any effect.

 

[Legacy 386498]

Just for curiosity, if I need to change columns, so the item list is in column B and numbers are in column Y, I tried this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long: LR = Range("B" & Rows.Count).End(xlUp).Row
Dim r As Range: Set r = Range("Y2:Y" & LR)
Dim tbl As Range: Set tbl = Range("B2:Y" & LR)


If Not Intersect(r, Target) Is Nothing Then
tbl.Sort Key1:=tbl.Cells(1, 2), Order1:=xlDescending, Header:=xlYes
End If


End Sub
​

But I have an error with the line: tbl.Sort Key1:=tbl.Cells(1, 2), Order1:=xlDescending, Header:=xlYes and it becomes yellow. How can I fix it?

Also, if I would like to change the range to be fix, I guest I would change:

Dim r As Range: Set r = Range("B2:B4" & LR)
Dim tbl As Range: Set tbl = Range("A2:B4" & LR)

Is that right? Thanks for taking the time!

 

[lrobbo314]

It looks like you've done it correctly to me. The only thing that should be changed is the sort key. In the test data we were sorting by column 2 of the table. Now, we want to sort column Y, so we should change this line.

[COLOR=#333333]bl.Sort Key1:=tbl.Cells(1, [/COLOR][B][COLOR=#0000cd]25[/COLOR][/B][COLOR=#333333]), Order1:=xlDescending, Header:=xlYes[/COLOR]

Don't think that will fix your problem, but it's worth a shot.

And this is how you would declare a fixed range.

[COLOR=#333333]Dim r As Range: Set r = Range("B2:Y100")[/COLOR]

 

[Legacy 386498]

I already tried changing it to 25, but it didn't work. Good to know for fixed range. Thanks.

 

[lrobbo314]

Thinking about it now, it looks like you might not have anything in column A. If that's the case you'll need to change the line below. This line is getting the last used row in a given column. So changing it to B might fix the problem.

[COLOR=#333333]Dim LR As Long: LR = Range("B" & Rows.Count).End(xlUp).Row[/COLOR]

 

[Legacy 386498]

Yeah, I already tried that to no avail, but now it's working when I put 24 (Y is the 25th letter, but if you think of it like a vlookup, from B to Y it's 24 columns). When I try putting a fixed range, so from row 2 to 9 for example and I enter a number in row 11, excel will sort it with the rest even if it isn't in the range specified.