Sorting Scores High to Low

[S Walker]

Good Afternoon,
I am trying to automatically sort scores in a table from high to low using VBA in Office 2019. The table is B2-13 & C2-13.
I found some code on Youtube that worked in the video but not in my workbook. Code is below. Am I looking in the wrong place?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 3 Then

 Dim lastrow As Long
 lastrow = Cells(Rows.Count, 3).End(xlUp).Row
 Range("B2:C" & lastrow).Sort key1:=Range("C2:C" & lastrow), order1:=xlDecending, Header:=xlNo
 
 End If

End Sub

Many Thanks for your help.
Steve Walker

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Also, when code "does not work" it helps if you give some details like

• It crashes my Excel
• It gives and error message of "..." on the line ...
• It does nothing
• It puts a 3 in B5 when it should be 7 because ..
• etc

Could it be that your code gives an error message because of the spelling error with xlDescending?

If that is not it then review the bullet points above.

 

[S Walker]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Also, when code "does not work" it helps if you give some details like

• It crashes my Excel
• It gives and error message of "..." on the line ...
• It does nothing
• It puts a 3 in B5 when it should be 7 because ..
• etc

Could it be that your code gives an error message because of the spelling error with xlDescending?

If that is not it then review the bullet points above.

Thanks for spotting the spelling mistake Peter.
Even with that corrected the programme does nothing
SW

 

[Peter_SSs]

Even with that corrected the programme does nothing

Thanks
Two things to try initially

1. Check where the code is placed. If you right-click the relevant sheet name tab and choose 'View Code' does the code immediately appear in the main right hand window pane that opens. (It should)
   
   
2. It is possible that you 'events' have become disabled. Save and close the file and close right out of Excel then open up again and test by changing a value in col C that should result in a change of order.

 

[S Walker]

Thanks
Two things to try initially

1. Check where the code is placed. If you right-click the relevant sheet name tab and choose 'View Code' does the code immediately appear in the main right hand window pane that opens. (It should)
   
   
2. It is possible that you 'events' have become disabled. Save and close the file and close right out of Excel then open up again and test by changing a value in col C that should result in a change of order.

Perfect! I didnt understand that the code was relative to an individual sheet and not the whole workbook.
Many Thanks Peter.

 

[Peter_SSs]

You're welcome. Glad that sorted it. Thanks for the confirmation.

 

[Joe4]

S Walker,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.

 

[S Walker]

You're welcome. Glad that sorted it. Thanks for the confirmation.

Just a question about the above code Peter. I have Four separate sheets with the same code in now. Is it possible to have them all on one sheet, with different Column letters. to save Tab space?
Please forgive me if that was a silly question, I'm 72 and only discovered Excel about a year or so ago.
SW

 

[Peter_SSs]

Please forgive me if that was a silly question,

No silly questions here - we were all beginners once and the forum is here to help anybody, whatever level.

I am still a little unclear on just what you have, where it is and what you would like to have and where that would be.
Perhaps you could make up a dummy workbook with small sets of data (only need a few rows) post that and then change it to what you would like and also post that with any further clarification. It would be great if you could do that posting samples with XL2BB because then we can see exactly where things are and can also copy the sample data/layout for testing.
Also we don't need four worksheets, two should do to demonstrate.

(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[S Walker]

No silly questions here - we were all beginners once and the forum is here to help anybody, whatever level.

I am still a little unclear on just what you have, where it is and what you would like to have and where that would be.
Perhaps you could make up a dummy workbook with small sets of data (only need a few rows) post that and then change it to what you would like and also post that with any further clarification. It would be great if you could do that posting samples with XL2BB because then we can see exactly where things are and can also copy the sample data/layout for testing.
Also we don't need four worksheets, two should do to demonstrate.

(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thank you Peter, Please bear with me whilst I try and get my head around XL2BB
SW