Sorting Data Fields in Excel 2010

Pancarta de nota rápida de Office

Handy Programming Tips for Microsoft Excel 2010: Learn how to sort multiple fields in a Microsoft Excel 2010 table of data.

Última modificación: jueves, 05 de mayo de 2011

Hace referencia a: Excel 2010 | Office 2010 | VBA

En este artículo
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Applies to:    Microsoft Excel 2010

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

Sorting data is an important part of data analysis. For example, you might want to alphabetize a list of names or compile a list of products organized by inventory levels. Sorting data helps you quickly visualize and understand your data, organize and find the data that you want, and ultimately make better decisions. In this topic, you programmatically create a table of data and then sort two columns. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that creates a table and then sorts two columns in that table.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.


    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub TestWorksheetSort()
       ' Create sample data:
      Range("A1:D1") = Array("Name", "City", "State", "Age")
      Range("A2:D2") = Array("Pam", "Los Angeles", "CA", "16")
      Range("A3:D3") = Array("Jerry", "Boston", "MA", "14")
      Range("A4:D4") = Array("Juanita", "San Francisco", "CA", "15")
      Range("A5:D5") = Array("Xochitl", "Houston", "TX", "11")
      Range("A6:D6") = Array("Jozi", "New York", "NY", "7")
      Range("A7:D7") = Array("Aneka", "Houston", "TX", "18")
      Range("A8:D8") = Array("Brie", "Boston", "MA", "22")
      Range("A9:D9") = Array("Andrew", "Seattle", "WA", "23")
      Range("A10:D10") = Array("Grace", "Boston", "MA", "35")
      Range("A11:D11") = Array("Tom", "Houston", "TX", "12")
      ' Retrieve a reference to the used range:
      Dim rng As Range
      Set rng = UsedRange
      ' Create sort:
      Dim srt As Sort
      ' Include these two lines to make sure you get
      ' IntelliSense help as you work with the Sort object:
      Dim sht As Worksheet
      Set sht = ActiveSheet
      Set srt = sht.Sort
      ' Sort first by state ascending, and then by age descending.
      srt.SortFields.Add Key:=Columns("C"), _
       SortOn:=xlSortOnValues, Order:=xlAscending
      srt.SortFields.Add Key:=Columns("D"), _
       SortOn:=xlSortOnValues, Order:=xlDescending
      ' Set the sort range:
      srt.SetRange rng
      srt.Header = xlYes
      srt.MatchCase = True
      ' Apply the sort:
    End Sub

Test the Solution

In this task, you step through the VBA code that creates a table of names and other data and then sorts two columns; one in ascending order and another in descending order.

To run the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Press F8 to step through the code line-by-line and watch the code sort the data in Excel.

Next Steps