+ Reply to Thread
Results 1 to 7 of 7

Sorting Macro

Hybrid View

  1. #1
    Registered User
    Join Date
    05-28-2010
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Sorting Macro

    I'm attempted to create a macro to auto sort which i've managed to create but now would like to have it automatically run the sorting macro when certain cells values are changed..

    the information I want sorted is in the following range O13:Y16
    and would like it to auto sort when values are entered into the following ranges
    H11:H16 and J11:J16

    would like it to auto sort first by Pts (column X) then by GD (blended in column Y) then by GF (column U)
    would like it to also work with the other group standings but one step at a time lol

    thanks
    Attached Files Attached Files
    Last edited by XRunner; 06-17-2010 at 12:24 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sorting Macro

    password protected attachment
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-28-2010
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Sorting Macro

    my bad, thought I had unprotected it before i uploaded it.

    uploaded the unprotected one.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sorting Macro

    Looking at it now.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sorting Macro

    This macro is in the sheet module. Any changes to column H or J will cause all the groups to autosort so they are all updated all the time.
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
        For Each cell In Target
            If (cell.Column = 10 And cell <> "" And cell.Offset(, -2) <> "") Or _
                cell.Column = 8 And cell <> "" And cell.Offset(, 2) <> "" Then
                Range("O12:Y16").Sort Key1:=Range("X12"), Order1:=xlAscending, _
                    Key2:=Range("Y12"), order2:=xlAscending, header:=xlYes
                Range("O20:Y24").Sort Key1:=Range("X20"), Order1:=xlAscending, _
                    Key2:=Range("Y20"), order2:=xlAscending, header:=xlYes
                Range("O28:Y32").Sort Key1:=Range("X28"), Order1:=xlAscending, _
                    Key2:=Range("Y28"), order2:=xlAscending, header:=xlYes
                Range("O36:Y40").Sort Key1:=Range("X36"), Order1:=xlAscending, _
                    Key2:=Range("Y36"), order2:=xlAscending, header:=xlYes
                Range("O44:Y48").Sort Key1:=Range("X44"), Order1:=xlAscending, _
                    Key2:=Range("Y44"), order2:=xlAscending, header:=xlYes
                Range("O52:Y56").Sort Key1:=Range("X52"), Order1:=xlAscending, _
                    Key2:=Range("Y52"), order2:=xlAscending, header:=xlYes
                Range("O60:Y64").Sort Key1:=Range("X60"), Order1:=xlAscending, _
                    Key2:=Range("Y60"), order2:=xlAscending, header:=xlYes
                Range("O68:Y72").Sort Key1:=Range("X68"), Order1:=xlAscending, _
                    Key2:=Range("Y68"), order2:=xlAscending, header:=xlYes
            End If
        Next cell
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-28-2010
    Location
    Edmonton, Alberta, Canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Sorting Macro

    YOU are my hero, I attempted that and I think i was about 25% right on getting their which isnt bad for just reading tutorials hahaha, appreciate it sooo much

    i've also attempted to make the top 2 teams appear in bold im assuming its all in the conditional format but when I attempt that and set it to the top 2 it just highlights all the 2 highest numbers instead of the 2 rows with the 2 topped ranked teams..

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Sorting Macro

    So, you need 2 of the 4 rows in each group highlighted? Really? Those are pretty short groups, that seems like a lot of work for an inordinately small list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1