Export (0) Print
Expand All

Sorting and Filtering a Table with the List Object in Excel 2010

Office 2010

Office Quick Note banner

Working with Tables in Microsoft Office 2010: Learn how to work with the ListObject object to change the sort and filter a table in Microsoft Excel 2010.

Applies to:    Microsoft Excel 2010

Published:   June 2011

Provided by:    Frank Rice, Microsoft Corporation

The ListObject object displays data in a series of rows and columns. In this topic, you programmatically work with members of the ListObject object to sort and filter the data in a table in Microsoft Excel 2010. To complete this task, you must do the following:

Add the Code to the Visual Basic Editor

In this task, you add programming code that manipulates the window properties in Excel.

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.

    noteNote:

    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 ListObjectSortFilterDemo()
        ' Step over this procedure. It's not terribly interesting.
        FillRandomData
       
        Dim lo As ListObject
        Set lo = ListObjects.Add( _
         SourceType:=xlSrcRange, _
         Source:=Range("A1:F13"), _
         XlListObjectHasHeaders:=xlYes)
        lo.Name = "SampleData"
       
        Dim so As Sort
        With lo.Sort
            With .SortFields
                .Clear
                .Add Range("SampleData[[#All], [Total]]"), SortOn:=xlSortOnValues, Order:=xlAscending
            End With
            .Header = xlYes
            .Orientation = xlSortColumns
            .Apply
        End With
           
        ' Set up a simple filter, giving values between 150 and 200:
        lo.Range.AutoFilter Field:=6, Criteria1:=">150", Operator:=xlAnd, Criteria2:="<200"
        ' Now clear the filter:
        lo.Range.AutoFilter Field:=6
       
        ' Filter for the top 10 percent, which should only be one item:
        lo.Range.AutoFilter Field:=6, Operator:=xlTop10Percent
       
        ' Delete the List object so you can run the code again if you like.
        lo.Delete
    End Sub
    
    Sub FillRandomData()
        ' No need to stop through this procedure.
        Range("A1", "F1").Value = Array("Month", "North", "South", "East", "West", "Total")
       
        ' Fill in twelve rows with random data.
        Dim i As Integer
        Dim j As Integer
        For i = 1 To 12
            Cells(i + 1, 1).Value = MonthName(i, True)
            For j = 2 To 5
                Cells(i + 1, j) = Round(Rnd * 100)
            Next j
        Next i
        Range("F2", "F13").Formula = "=SUM(B2:E2)"
    End Sub
    
    

Test the Solution

In this task, you add a table with data to a worksheet and then step through the VBA code to see the effects sorting and filtering the data.

To step through 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. Now, place the cursor in ListObjectSortFilterDemo procedure, press F8 to get started debugging, and then press Shift+F8 to step through the code line-by-line (and bypass external procedures) and watch the code behavior.

Next Steps

Community Additions

ADD
Show:
© 2014 Microsoft