Sort the Excel rows based on the values in a cell

- 9/30/2014


There are two ways to do this -

Option 1:

Sort rows by two or three criteria (columns)

For best results, the range you sort should have column labels, or headers.

1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the Sort by and Then by boxes, click the columns you want to sort, starting with the most important.
4. Select any other sort options you want, and then click OK.

Option 2:

a. Record macro by performing the activity or write your own code.

Below is the used to perform the sorting operation.

 'Sorting the rows
Sub Macro1()
'
' Macro1 Macro
'
'    'Select the range of cells
    Range("A2:K10").Select
    
     'clearing the sheet for any sorting that already exist
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
     '
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("H2:H19") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:K19")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=-3
End Sub

Deatils of syntax of sort method are here:

https://msdn.microsoft.com/en-us/library/office/aa213557(v=office.11).aspx

Example

This example sorts the range A1:C20 on Sheet1, using cell A1 as the first sort key and cell B1 as the second sort key. The sort is done in ascending order by row, and there are no headers. This example assumes there is data in the range A1:C20.

Sub SortRange1()
   Worksheets("Sheet1").Range("A1:C20").Sort _
        Key1:=Worksheets("Sheet1").Range("A1"), _
        Key2:=Worksheets("Sheet1").Range("B1")
End Sub
  

This example sorts the region that contains cell A1 (the active region) on Sheet1, sorting by the data in the first column and automatically using a header row if one exists. This example assumes there is data in the active region, which includes cell A1. TheSort method determines the active region automatically.

Sub SortRange2()
   Worksheets("Sheet1").Range("A1").Sort _
        Key1:=Worksheets("Sheet1").Columns("A"), _
        Header:=xlGuess
End Sub