Trying to Sort Column A with some Visual Basic Code

[RudeBoy]

I have some Visual basic code that is sorting but not what I am wanting
I am not strong in this area so am asking for some Help - Thanks

Here is my code: I will also upload a pic of the data thanks so much for any & all help

Sub Worksheet_Activate()
'
' RnkSrt Macro
' Macro add on 06/25/2024 by Rude Boy Chris
'
  rowcount = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(9, 1), Cells(rowcount, 16)).Select
   
    Selection.Sort Key1:=Range("A9"), Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    Range("A1").Activate
End Sub

 

[Sektor]

I guess your "Selection" doesn't start with row 9 that's why Key1 (cell A9) doesn't fit.

 

[RudeBoy]

I guess your "Selection" doesn't start with row 9 that's why Key1 (cell A9) doesn't fit.

When I change it to 1,1 I receive this error? Helpppppppppppp

 

[Sektor]

Your header starts with 9-th row (Range("A9")), but your selection starts with 1-st row (Cells(1, 1)).

 

[RudeBoy]

Your header starts with 9-th row (Range("A9")), but your selection starts with 1-st row (Cells(1, 1)).

I guess me & Visual Basic do not get along. I changed what you said but still an error Thanks for your help

I will upload pic of error & the code I modified & ran but received an error

 

[rlv01]

Start with this:

Sub Worksheet_Activate()
    'RnkSrt Macro
    Dim SortRange As Range, RowCount As Long, StartRow As Long
    
    With Me
        StartRow = 9
        RowCount = .UsedRange.Rows.Count
        If RowCount > StartRow Then
            Set SortRange = .Range(.Cells(9, 1), .Cells(RowCount, 16))
            SortRange.Sort Key1:=.Cells(9, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        Else
            MsgBox "No data after Row #" & StartRow & ". There is nothing to sort."
        End If
        .Range("A1").Activate
    End With
End Sub

If it is not doing what you want, then explain in detail.

 

[Sektor]

Could you upload your worksheet to cloud?

 

[RudeBoy]

Start with this:

Sub Worksheet_Activate()
    'RnkSrt Macro
    Dim SortRange As Range, RowCount As Long, StartRow As Long
   
    With Me
        StartRow = 9
        RowCount = .UsedRange.Rows.Count
        If RowCount > StartRow Then
            Set SortRange = .Range(.Cells(9, 1), .Cells(RowCount, 16))
            SortRange.Sort Key1:=.Cells(9, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        Else
            MsgBox "No data after Row #" & StartRow & ". There is nothing to sort."
        End If
        .Range("A1").Activate
    End With
End Sub

If it is not doing what you want, then explain in detail.

That works Awesomeeeeeeeeeeeeeeeeeeeeeee

 

[RudeBoy]

Start with this:

Sub Worksheet_Activate()
    'RnkSrt Macro
    Dim SortRange As Range, RowCount As Long, StartRow As Long
   
    With Me
        StartRow = 9
        RowCount = .UsedRange.Rows.Count
        If RowCount > StartRow Then
            Set SortRange = .Range(.Cells(9, 1), .Cells(RowCount, 16))
            SortRange.Sort Key1:=.Cells(9, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        Else
            MsgBox "No data after Row #" & StartRow & ". There is nothing to sort."
        End If
        .Range("A1").Activate
    End With
End Sub

If it is not doing what you want, then explain in detail.

I am trying to use this code to Auto Sort two Rows having the Higher number in this case 2866 be at the top.

You had given this code to me a while back & it is working great on another excel program but I am trying to use it here also.

Here is the data trying to sort

SportingNews.com Fantasy Source - NFL

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

I am using the code you built goo stuff:

Sub Worksheet_Activate()
'RnkSrt Macro
Dim SortRange As Range, RowCount As Long, StartRow As Long

With Me
StartRow = 1
RowCount = .UsedRange.Rows.Count
If RowCount > StartRow Then
'Set SortRange = .Range(.Cells(9, 1), .Cells(RowCount, 16))
Set SortRange = .Range(.Cells(1, 1), .Cells(RowCount, 2))

'SortRange.Sort Key1:=.Cells(9, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
SortRange.Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Else
MsgBox "No data after Row #" & StartRow & ". There is nothing to sort."
End If
.Range("A1").Activate
End With
End Sub



The code may need a bit of tweaking let me know thanks

 

[Peter_SSs]

I am trying to use this code to Auto Sort two Rows having the Higher number in this case 2866 be at the top.

You had given this code to me a while back & it is working great on another excel program but I am trying to use it here also.

Here is the data trying to sort

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

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.

Also, you have been asked three times in that other thread to use code tags when posting code yet you are still posting code here without the tags. Forum members are trying to help you (for no reward) with your problems. It would seem sensible to provide your information in the best possible way to make it easier for them to do so.