locked
How to sort by value RRS feed

  • Question

  • Hi All,

    I whave a colum with 6 different values.

    I would like to sort those values by a specific order. Hoe do i do that?

    Thanks for your help

    Amir

     

    Saturday, December 24, 2011 9:07 PM

Answers

  • Sub ChickenNoodleSoup()
     Dim rng As Range
     Dim vNumbers As Variant
     Set rng = Selection.Columns(1).Cells
     Set rng = rng.Resize(, 2)
     vNumbers = Array(2, 3, 6, 4, 1, 5)
     rng.Columns(2).Value2 = Application.WorksheetFunction.Transpose(vNumbers)
     rng.Sort key1:=rng(1, 2), Order1:=xlAscending
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.contextures.com/excel-sort-addin.html
    (editorial review of special sort excel add-in (30 ways to sort)
    • Proposed as answer by Alfidai Sunday, December 25, 2011 8:14 AM
    • Marked as answer by danishani Thursday, January 12, 2012 3:21 AM
    Sunday, December 25, 2011 6:29 AM

All replies


  • In an adjoining column, add a number opposite each value equal to the desired position...

    Huey      2
    Dewey    1
    Louie      6
    Alpha     4
    Beta      5
    Chi        3

    Select both columns and sort by the column with the numbers.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.contextures.com/excel-sort-addin.html
    (editorial review of special sort excel add-in (30 ways to sort)


    • Edited by Jim Cone Sunday, December 25, 2011 3:48 AM
    Sunday, December 25, 2011 3:47 AM
  • Hi,

     

    Can you write an example code?

    I 'm nit sure regarding the syntex 

    Sunday, December 25, 2011 5:38 AM
  • Sub ChickenNoodleSoup()
     Dim rng As Range
     Dim vNumbers As Variant
     Set rng = Selection.Columns(1).Cells
     Set rng = rng.Resize(, 2)
     vNumbers = Array(2, 3, 6, 4, 1, 5)
     rng.Columns(2).Value2 = Application.WorksheetFunction.Transpose(vNumbers)
     rng.Sort key1:=rng(1, 2), Order1:=xlAscending
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.contextures.com/excel-sort-addin.html
    (editorial review of special sort excel add-in (30 ways to sort)
    • Proposed as answer by Alfidai Sunday, December 25, 2011 8:14 AM
    • Marked as answer by danishani Thursday, January 12, 2012 3:21 AM
    Sunday, December 25, 2011 6:29 AM