Automatically rank list based on numbers from highest to lowest

levedge

New Member
Joined
Oct 11, 2016
Messages
40
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>
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,486
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
 

levedge

New Member
Joined
Oct 11, 2016
Messages
40
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

Well-known Member
Joined
Jul 14, 2008
Messages
2,486
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.
 

levedge

New Member
Joined
Oct 11, 2016
Messages
40
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:

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,486
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]
 

levedge

New Member
Joined
Oct 11, 2016
Messages
40
I already tried changing it to 25, but it didn't work. Good to know for fixed range. Thanks.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,486
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]
 

levedge

New Member
Joined
Oct 11, 2016
Messages
40
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.
 

Forum statistics

Threads
1,077,827
Messages
5,336,617
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top