• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Sorting Question

Hi.. wvklan,

Welcome to the forum..

Try this..

Code: 
Sub SortMulArea()
With Worksheets("Standings").UsedRange
    Set c = .Find("Code", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            lr = c.CurrentRegion.Rows.Count - 1
            lc = c.End(xlToRight).Column - c.Column + 1
            Set sortrange = c.Resize(lr, lc)
            ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Standings").Sort.SortFields.Add Key:=sortrange.Columns(lr).Offset(1).Resize(lr - 1), _
                SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            With ActiveWorkbook.Worksheets("Standings").Sort
                .SetRange sortrange
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

Please let us know if you need any further assistance...
 
Thank you so much! Do ou happen to know f this will update when changes are made to the schedule sheet or will this have to be run everytime? If so, is there a way to make it update/run automatically with some sort of query/script
 
We can modify and shoot the bullet as per your request.. :)
but i would like to suggest you run it everytime, wheneever you need to view.. coz sorting so many blocks with each calculation.. may cause poor performance.. use a Button or Keyboard shortcut.. to run the query..

lemme me know if you need to place a button in sheet or need to assign a shortcut for this code..

thanks for feedback.. :)
 
I currently am using a button but was just curious if there was an easy way to make it "auto" update/sort after changes were made to te first sheet. Would a shortcut be more convenient?
 
Your sheet is protected.. thats means you want some privacy.. so i would suggest to go for button only.
still if you need you can call this macro from Sheet_activate trigger..
 
I've got one last question:
If there is a tie (IE: the win % is the same for 1 or more teams, how can I add to the macro to sort the win % by team name?
 
Hi, wvklan!
My two cents in addition to Debraj's post, a cent per link, check these 2:
http://chandoo.org/forum/threads/compare-multiple-columns-between-files.12314/#post-72265
http://chandoo.org/forum/threads/vba-help-with-data-copy.12324/#post-72398
In both there're sort snippets, both starting from a range, the 1st working on the whole worksheet and the 2nd one on just that range (3 in the example)
I'm pretty sure that Debraj will update his code or if he wants he'll be as kind as to do my job and borrow the code without my permission and adapt it to your requirement. As he usually does, BTW.
If not, give him some time, don't rush him, just advise and I'll come back to you.
Regards!

@Debraj
Hi, buddy!
I'm counting on you, ha ha...
Regards!
PS: I liked the 2nd link code :)
 
Please find the updated one.. :)

Code: 
Sub SortMulArea()
With Worksheets("Standings").UsedRange
    Set c = .Find("Code", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            lr = c.CurrentRegion.Rows.Count - 1
            lc = c.End(xlToRight).Column - c.Column + 1
            Set sortrange = c.Resize(lr, lc)
            With ActiveWorkbook.Worksheets("Standings").Sort
                .SortFields.Clear
                .SortFields.Add Key:=sortrange.Columns(lr).Offset(1).Resize(lr - 1), _
                    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                .SortFields.Add Key:=sortrange.Columns(1).Offset(1).Resize(lr - 1), _
                    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
               
                .SetRange sortrange
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub
 
AMAZING!!! Thank you sooo much! How long have you been using excel? Im assuming you know VB and probably a few other languages?
 
Hi Wvklan..

Wvklan said:
Thank you sooo much
Click to expand...

you dont have to say those long words.. just click the Like button in the post.. if it helps you.. ;)

Yes.. I know few other languages also..
Hindi, Bengali, Punjabi, and learning french also.
 
You must log in or register to reply here.
Back
Top