Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Sort column by VBA or formula

  1. #1
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    385
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Sort column by VBA or formula

    Hi All

    I need to sort column A first by the lowest number. Column A is always a number.Then by Column B and is always a word with many duplicates.

    Example:
    Sheet 1

    _____A______B
    1____21_____Red
    2____22_____Red
    3____23_____Red
    4____103____Green
    5____104____Green
    6____105____Green
    7____67_____Blue
    8____13_____Blue
    9____74_____Blue
    10___32_____Green
    11___14_____Blue
    12___82_____Red
    13___1______Red
    14___2______Red
    15___3______Red
    16___55_____Blue
    17___56_____Blue
    18___57_____Blue

    Result is just the number in column A in sheet 2:
    _____A
    1_____1 (Red)
    2_____2 (Red)
    3_____3 (Red)
    4____21 (Red)
    5____22 (Red)
    6____23 (Red)
    7____82 (Red)
    8____13 (Blue)
    9____14 (Blue)
    10___55 (Blue)
    11___56 (Blue)
    12___57 (Blue)
    13___67 (Blue)
    14___74 (Blue)
    15___32 (Green)
    16__103 (Green)
    17__104 (Green)
    18__105 (Green)

    If this is best done with a macro, I would like it to run when the sheet is calculated.
    I know this can be done using Sort, but I need it to be automated.

    Thank you!

    Russ
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sort column by VBA or formula

    Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.
    Code:
    Private Sub Worksheet_Calculate()
        Application.ScreenUpdating = False
        Dim LastRow As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.ScreenUpdating = True
        Sheets("Sheet1").Sort.SortFields.Clear
        Sheets("Sheet1").Sort.SortFields.Add Key:=Range("B1:B" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        Sheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With Sheets("Sheet1").Sort
            .SetRange Range("A1:B" & LastRow)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    385
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort column by VBA or formula

    Hi Mumps
    I see that this sorts column A and B in sheet 1 perfectly. My goal though is to leave sheet 1 intact and have column A in sheet 2 just show the sorted numbers from sheet 1.

    ADJUSTMENT: I need to adjust Sheet1. Column A is still the number but starts at A3. Column K is the word and starts at K3.

    I appreciate your help!

    Thanks!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sort column by VBA or formula

    You said that you wanted the macro "to run when the sheet is calculated". Is it Sheet1 or Sheet2 that is calculated? Since the data is in columns A and K in Sheet1, do you want the end result in columns A and K of Sheet2 or in columns A and B of Sheet2?
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  5. #5
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    385
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort column by VBA or formula

    I would like the macro in sheet 2 to run when it is calculated.
    I need the end result to be in Sheet 2 column A only. I just need the number to appear in column A (Start in A3). I don't need the name to appear in sheet 2. Column K in sheet 1 is just needed for sorting purposes.


    Thank you!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  6. #6
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sort column by VBA or formula

    Try this macro in the code module for Sheet2:
    Code:
    Private Sub Worksheet_Calculate()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim LastRow As Long, srcWS As Worksheet
        Set srcWS = Sheets("Sheet1")
        Columns("B:B").Insert Shift:=xlToRight
        Columns("A").ClearContents
        With srcWS
            .Range("A3", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Copy Cells(1, 1)
            .Range("K3", srcWS.Range("K" & srcWS.Rows.Count).End(xlUp)).Copy Cells(1, 2)
        End With
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.ScreenUpdating = True
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range("B1:B" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveSheet.Sort.SortFields.Add Key:=Range("A1:A" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("A1:B" & LastRow)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Columns("B").Delete
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  7. #7
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    385
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort column by VBA or formula

    I will try this later tonight.
    Thank you very much for your help!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  8. #8
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    385
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort column by VBA or formula

    This is really close to what I need. I just need the results in Sheet 2 to start in A3. I tried to tweak your code but didn't get the proper results.
    Also, the macro doesn't run when I hit calculate. I can run it when I hit play from the developer tab.
    Thank you for your help!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

  9. #9
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    7,873
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Sort column by VBA or formula

    This will make the results in Sheet 2 start in A3. The macro is a Worksheet_Calculate event and is triggered when any formula in Sheet2 is calculated by changing a value referenced in the formula. For example, if you have this formula in Sheet2: =sum(A1:B1) in any cell, if you change either the value in A1 or B1 the macro will be triggered because the formula is then calculated. If you want to trigger the macro manually by clicking a button, then we have to change our approach.
    Code:
    Private Sub Worksheet_Calculate()
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim LastRow As Long, srcWS As Worksheet
        Set srcWS = Sheets("Sheet1")
        Columns("B:B").Insert Shift:=xlToRight
        Columns("A").ClearContents
        With srcWS
            .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Copy Cells(3, 1)
            .Range("K1", .Range("K" & .Rows.Count).End(xlUp)).Copy Cells(3, 2)
        End With
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Application.ScreenUpdating = True
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range("B3:B" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ActiveSheet.Sort.SortFields.Add Key:=Range("A3:A" & LastRow) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveSheet.Sort
            .SetRange Range("A3:B" & LastRow)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Columns("B").Delete
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  10. #10
    Board Regular
    Join Date
    May 2006
    Location
    New Jersey
    Posts
    385
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sort column by VBA or formula

    Almost there! Hope I'm not asking too much here.
    I believe the macro is sorting column B alphabetically from the tests that I have tried.
    I need column A to sort by the lowest number first. Then sort column B with the matching names.
    Example:
    11___Apple
    12___Apple
    1___Banana
    2____Banana
    7____Orange
    8____Orange
    31___Banana
    32___Orange
    33___Apple
    The desired result would be:
    1
    2
    31
    7
    8
    32
    11
    12
    33
    So, if I were to swop Apple with Banana, the result above would be the same.
    Thanks for all your help so far!
    Words alone cannot explain how grateful that I am to all the people here
    that take the time to help others and myself!
    Grateful member since 2006.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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