Automatically rank list based on numbers from highest to lowest

L

Legacy 386498

Guest
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.

Candy5
Chocolate2
Cake-1

<tbody>
</tbody>
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Something like this?

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.

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

Adding and deleting rows shouldn't have any effect.
 
Upvote 0
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!
 
Last edited by a moderator:
Upvote 0
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.

Code:
[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.

Code:
[COLOR=#333333]Dim r As Range: Set r = Range("B2:Y100")[/COLOR]
 
Upvote 0
I already tried changing it to 25, but it didn't work. Good to know for fixed range. Thanks.
 
Upvote 0
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.

Code:
[COLOR=#333333]Dim LR As Long: LR = Range("B" & Rows.Count).End(xlUp).Row[/COLOR]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top