Trying to do an Auto Sort when I enter Tab using VBA Code

[RudeBoy]

Hello Everyone,
I am trying to sort column A in my Tab & have the larger number sort to the top once I enter my tab.

It is running when I enter but my syntax must be incorrect as the largest number in Column A in Excel 365 is not sorting to the top.

Helppppppp Please advise - Thanks so much

Here is the VBA code I am using:

Sub Worksheet_Activate()
'
' RnkSrt Macro
'
    RowCount = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(1, 1), Cells(RowCount, 2)).Select
   
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Range("A1").Activate
End Sub

 

[Logit]

Strange ... your macro code works here as is. I'm using Excel 2007 on Win 10/64 bit

 

[RudeBoy]

Strange ... your macro code works here as is. I'm using Excel 2007 on Win 10/64 bit

Yeah i think office 365 stinks

 

[Peter_SSs]

the largest number in Column A in Excel 365 is not sorting to the top.

That is because your code has Order1:=xlAscending when it should be Order1:=xlDescending
if you want largest at the top
Also ..

1. 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.
   
   
2. I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[RudeBoy]

That is because your code has Order1:=xlAscending when it should be Order1:=xlDescending
if you want largest at the top
Also ..

1. 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.
   
   
2. I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks i will try that when i return home

 

[RudeBoy]

Thanks i will try that when i return home

Put Descending but I still see the numbers below values are still not sorting properly? Please advise thanks


SportingNews.com Fantasy Source - NFL

#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
#VALUE!	#VALUE!
800	28a Willie Parawan
60	03c Jay Butler
460	09d Brian Caandoy
320	18b Joseph Martinez
290	01a Raymann Patao
2866	01c Robert Contreras
130	19d Mark Near
110	03d Edgar Jurado
100	14c Joel Cheng

 

[RudeBoy]

Thanks i will try that when i return home

Here is the VBA code is a setting not on or something? Helpppppp

Sub Worksheet_Activate()
'
' RnkSrt Macro
' Modified on 07/26/2004 by Rude Boy Chris
'
RowCount = ActiveSheet.UsedRange.Rows.Count
Range(Cells(1, 1), Cells(RowCount, 2)).Select

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Activate
End Sub

 

[RudeBoy]

Strange ... your macro code works here as is. I'm using Excel 2007 on Win 10/64 bit

Yep this VBA sort has worked for years on other version of Excel but does not seem happy on 365 version?? Maybe a different setting is not on not sure?

 

[Peter_SSs]

Here is the VBA code

Please read numbered point 1. in my previous post

 

[Peter_SSs]

Do those #VALUE! cells contain formulas or have they been converted to text values only?
In any case the sort will treat them as text values and text values are always treated by Excel as greater than numerical values so will get sorted to the top with a descending sort followed by numbers in descending order. This is exactly what happens with your data and code once Ascending is changed to Descending, so I don't see anything happening incorrectly

Before:

RudeBoy.xlsm
	A	B
1	#VALUE!	#VALUE!
2	#VALUE!	#VALUE!
3	#VALUE!	#VALUE!
4	#VALUE!	#VALUE!
5	#VALUE!	#VALUE!
6	#VALUE!	#VALUE!
7	#VALUE!	#VALUE!
8	#VALUE!	#VALUE!
9	#VALUE!	#VALUE!
10	#VALUE!	#VALUE!
11	#VALUE!	#VALUE!
12	800	28a Willie Parawan
13	60	03c Jay Butler
14	460	09d Brian Caandoy
15	320	18b Joseph Martinez
16	290	01a Raymann Patao
17	2866	01c Robert Contreras
18	130	19d Mark Near
19	110	03d Edgar Jurado
20	100	14c Joel Cheng
Sheet1

Sub Worksheet_Activate()
'
' RnkSrt Macro
'
    RowCount = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(1, 1), Cells(RowCount, 2)).Select
   
    Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Range("A1").Activate
End Sub

After:

RudeBoy.xlsm
	A	B
1	#VALUE!	#VALUE!
2	#VALUE!	#VALUE!
3	#VALUE!	#VALUE!
4	#VALUE!	#VALUE!
5	#VALUE!	#VALUE!
6	#VALUE!	#VALUE!
7	#VALUE!	#VALUE!
8	#VALUE!	#VALUE!
9	#VALUE!	#VALUE!
10	#VALUE!	#VALUE!
11	#VALUE!	#VALUE!
12	2866	01c Robert Contreras
13	800	28a Willie Parawan
14	460	09d Brian Caandoy
15	320	18b Joseph Martinez
16	290	01a Raymann Patao
17	130	19d Mark Near
18	110	03d Edgar Jurado
19	100	14c Joel Cheng
20	60	03c Jay Butler
Sheet1