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

R

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
431
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code: 
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
 
Last edited by a moderator:
Sort by date Sort by votes
Peter_SSs said:
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
AB
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!
1280028a Willie Parawan
136003c Jay Butler
1446009d Brian Caandoy
1532018b Joseph Martinez
1629001a Raymann Patao
17286601c Robert Contreras
1813019d Mark Near
1911003d Edgar Jurado
2010014c Joel Cheng
Sheet1


Rich (BB code): 
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
AB
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!
12286601c Robert Contreras
1380028a Willie Parawan
1446009d Brian Caandoy
1532018b Joseph Martinez
1629001a Raymann Patao
1713019d Mark Near
1811003d Edgar Jurado
1910014c Joel Cheng
206003c Jay Butler
Sheet1
Click to expand...
Well I cannot seem to get the XL2BB working and after deleting the Value error rows it still is not sorting correctly so I am at a loss :-(
Not sure why?

80028a Willie Parawan
6003c Jay Butler
46009d Brian Caandoy
32018b Joseph Martinez
29001a Raymann Patao
286601c Robert Contreras
13019d Mark Near
11003d Edgar Jurado
10014c Joel Cheng


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

When this is run on older versions of excel it works fine but for some reason Office 365 version of excel has weird results :-(
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Please read numbered point 1. in post #4

RudeBoy said:
Well I cannot seem to get the XL2BB working
Click to expand...
What exactly goes wrong when you try and at what point in the instructions does that happen?

RudeBoy said:
but for some reason Office 365 version of excel has weird results
Click to expand...
The code works fine in 365 with the sample data I have so I expect it is related to your data.
What does the formula =ISNUMBER(A5) return if you replace the A5 with the cell reference to the 800 in that sample data?
 
Upvote 0
Peter_SSs said:
Please read numbered point 1. in post #4


What exactly goes wrong when you try and at what point in the instructions does that happen?


The code works fine in 365 with the sample data I have so I expect it is related to your data.
What does the formula =ISNUMBER(A5) return if you replace the A5 with the cell reference to the 800 in that sample data?
Click to expand...
When using the =ISNUMBER(A1) all the way down the result is FALSE for every item.



SportingNews.com Fantasy Source - NFL
10014c Joel Cheng
FALSE​
11003d Edgar Jurado
FALSE​
13019d Mark Near
FALSE​
286601c Robert Contreras
FALSE​
29001a Raymann Patao
FALSE​
32018b Joseph Martinez
FALSE​
46009d Brian Caandoy
FALSE​
6003c Jay Butler
FALSE​
80028a Willie Parawan
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
#VALUE!#VALUE!
FALSE​
 
Upvote 0
RudeBoy said:
When using the =ISNUMBER(A1) all the way down the result is FALSE for every item
Click to expand...
That explains why the numbers are not being sorted as you want - they are not in fact numbers, but text values. The sorting would be no different in older version of Excel, by the way.

It would help get to a resolution faster if you could please address any questions or points I raise in my posts, like my repeated request for you to post code correctly and also ..

1. Regarding XL2BB from post #12:
Peter_SSs said:
What exactly goes wrong when you try and at what point in the instructions does that happen?
Click to expand...

2. From post #10:
Peter_SSs said:
Do those #VALUE! cells contain formulas or have they been converted to text values only?
Click to expand...

.. and now I have one further question:
3. Do the 'number' value cells in column A have formulas in them or not?
 
Upvote 0
Peter_SSs said:
That explains why the numbers are not being sorted as you want - they are not in fact numbers, but text values. The sorting would be no different in older version of Excel, by the way.

It would help get to a resolution faster if you could please address any questions or points I raise in my posts, like my repeated request for you to post code correctly and also ..

1. Regarding XL2BB from post #12:


2. From post #10:


.. and now I have one further question:
3. Do the 'number' value cells in column A have formulas in them or not?
Click to expand...
When I get XL2BB to come up it says it is spam but I get passed that and I only see mr excel the other two fields are greyed out.
I added the XL2BB to the addins directory in Microsoft as you showed but I do not see it in add ins see picture.

The column A cells have formulas in them as I am pulling info in from another Tab - Thanks for your help sorry I cant seem to get XL2BB working.
I am on Windows 11 and using Office 365 thanks for your help
 

Attachments

  • Add ins.jpg
    Add ins.jpg
    22.8 KB · Views: 4
Upvote 0
RudeBoy said:
When I get XL2BB to come up it says it is spam but I get passed that and I only see mr excel the other two fields are greyed out.
Click to expand...
Thanks for that. Please try what is suggested in this thread.

RudeBoy said:
The column A cells have formulas in them
Click to expand...
Thanks. Would it be okay to alter the formulas so that the 'numbers' are actual numbers and not text? If so, and you are not able to make that change yourself, please post one of the existing formulas from column A.

BTW, also thanks for updating four profile to show Office version details etc.
 
Upvote 0
Peter_SSs said:
Thanks for that. Please try what is suggested in this thread.


Thanks. Would it be okay to alter the formulas so that the 'numbers' are actual numbers and not text? If so, and you are not able to make that change yourself, please post one of the existing formulas from column A.

BTW, also thanks for updating four profile to show Office version details etc.
Click to expand...
=IF('M-Mix-Dbls-Final-Pass'!$G4="0"," ",'M-Mix-Dbls-Final-Pass'!I4)

I put the XL2BB in a trusted site and still it is not coming up :-(

Put it here = C:\Program Files\Microsoft Office\root\Templates

pic-002.jpg
 

Attachments

  • pic-003.jpg
    pic-003.jpg
    47.2 KB · Views: 2
Upvote 0
RudeBoy said:
I put the XL2BB in a trusted site and still it is not coming up :(
Click to expand...
Did you also follow the last part of post #3 in that thread?


RudeBoy said:
=IF('M-Mix-Dbls-Final-Pass'!$G4="0"," ",'M-Mix-Dbls-Final-Pass'!I4)
Click to expand...
With a copy of your workbook I suggest that you try these steps.
  1. Change that formula to this one and copy up/down column A
    =IF('M-Mix-Dbls-Final-Pass'!$G4="0","",IFERROR(--'M-Mix-Dbls-Final-Pass'!I4,""))

  2. Replace the previous Worksheet_Activate code with this one and test.
VBA Code: 
Sub Worksheet_Activate()
  Dim RowCount As Long

  RowCount = ActiveSheet.UsedRange.Rows.Count
  With Range("A1:B" & RowCount)
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    With .SpecialCells(xlFormulas, xlNumbers)
      .Sort Key1:=.Cells(1), Order1:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
  End With
End Sub
 
Upvote 0
Solution
Peter_SSs said:
Thanks for that. Please try what is suggested in this thread.


Thanks. Would it be okay to alter the formulas so that the 'numbers' are actual numbers and not text? If so, and you are not able to make that change yourself, please post one of the existing formulas from column A.

BTW, also thanks for updating four profile to show Office version details etc.
Click to expand...

When you say include the tabs - How can I do that? I have not been able to get XL2BB to work in Office 365 unfortunately.
Thanks for your help.
 
Upvote 0
RudeBoy said:
I have not been able to get XL2BB to work in Office 365 unfortunately.
Click to expand...
It isn't 365 that is the problem as XL2BB works fine with it. Can you confirm that you removed the tick from the 'Unblock' box?

RudeBoy said:
When you say include the tabs - How can I do that?
Click to expand...
Where did I say that?

RudeBoy said:
Thanks for your help.
Click to expand...
You're welcome. Since you have marked post #18 as the solution I assume that the formula change and new code got you the results that you wanted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Locked
  • Solved
Trying to Sort Column A with some Visual Basic Code
Replies
9
Views
344
Peter_SSs
Peter_SSs
R
  • Solved
Is there a way for this VBA code to Ignore the spaces & give the results at the top of the page?
2
Replies
10
Views
649
Alex Blakenburg
A
G
  • Solved
Combine input box (multiple criteria)
Replies
4
Views
149
gomes123
G
E
  • Solved
Custom Sorting in VBA
Replies
3
Views
620
Micron
M
Z
  • Solved
Date and Time Not Sorting
2
Replies
19
Views
347
MARK858
MARK858

Forum statistics

Threads
1,224,817
Messages
6,181,144
Members
453,021
Latest member
Justyna P

Share this page

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