VBA Lesson 18: Excel VBA Vocabulary to Filter and Sort Data

This is an excerpt from Pierre Leclerc (www.excel-vba.com)

When Excel recognises your set of data as a database it offers you very powerful database functionalities like sorting and filtering.

Filtering

When you work in an Excel database you might want to make sure that all filters are off. To this end you will start your procedure with two "If"statements. You need to verify if the autofilters are on because the sentence Selection.AutoFilter turns the autofilters on if they are off and turns them off if they are on. As for advanced filter, you need to verify if they are on before you use ActiveSheet.ShowAllData because if no filters are on you will get an error message.

Exercise 1

Step 1: In a new workbook create a small database starting in cell A1 respecting the criteria imposed by Excel for a database.

Step 2: Use the ALT/F11 keys to move to the Visual Basic Editor.

Step 3: Copy/Paste the following macro from here into the code window of any sheet.

     Sub proLessson18a()
           Range("A1" ).Select
           Selection.AutoFilter
      End Sub

Notice that a cell within the database (A1 in the example) is first selected. If you select a cell out of the database you will get an error message

Step 4: Use the ALT/F11 keys to go back to Excel. Run the macro proLessson18a.

Notice that the auto filters have appeared in the database.

Step 5: Run the macro again

Notice that the auto filters are gone.

You have just discover that the sentence Selection.AutoFilter shows or hide the auto filters and it depends if they are on or off to begin with.

Exercise 2

So before you activate filters within a database you need to make sure that they are not already on.

Step 1: Use the ALT/F11 keys to move back to the Visual Basic Editor.

Step 2: Copy/Paste the following macro from here into the code window of any sheet.

      Sub proLessson18b()

           Range("A1" ).Select
           If ActiveSheet.AutoFilterMode = True Then
                Selection.AutoFilter
             End If 

           If ActiveSheet.FilterMode = True Then
                ActiveSheet.ShowAllData
           End If

      End Sub

Step 3: Use the ALT/F11 keys to go back to Excel.

Step 4: Run the macro proLessson18a then run proLessson18b. You see that the auto filters get turned off.

Before you complete step 5 try to activate different filters (auto or advanced) and run the macro again.

Step 5: Close Excel without saving anything

You should never write the code for filtering or sorting a set of data. Use the macro recorder and then copy/paste the code and modify it to suit your needs.

Note: Macros recorded to sort and filter data using Excel 2007 will not work in earlier versions of Excel. But you can use recordings made in Excel 1997 to 2006 and use them in workbooks created in Excel 2007.

For example: a recording to sort data looks like this before 2007:

Range("A1:E7").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range( _
        "B2"), Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal

In 2007 it looks like this:

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C7"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:E7")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

A slightly modified first example will work in any version of Excel with any database. The second example will only work in Excel 2007 even if you save your file as .XLS

Here is the universal sorting code. Notice that the range of the database has been replaced with Selection.CurrentRegion that you have discovered in lesson 16. A few lines at the end have been removed to avoid a bug with Excel 1997.

You can use this code with any Excel database in any version of Excel. You might want to remove Key1 and Key 3 if you don't need them. You will need to replace the addresses (A1, B1 and C1) with the addresses of the headers that you want to use to sort. And you might also want to replace xlAscending with xlDescending sometimes.

Sub proLessson18c()

   Range("A1").select

   Selection.CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range( _
        "B1"), Order2:=xlAscending, Key3:=Range("C1"), Order3:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom

End Sub

Exercise

Step 1: Create a small database ( 3 columns minimum) in Excel starting in cell A1.

Step 2: Use the ALT/F11 keys to move to the Visual Basic Editor.

Step 3: Copy/Paste the macro above from here into the code window of any sheet.

Step 4: Use the ALT/F11 keys to go back to Excel and run the macro proLessson18c.

Repeat this exercise by moving the small database in cell G6, by modifying the parameters. Notice that a cell within the database must be selected for this code to work. For example, if you start with an address outside the database at the beginning (U34 for example) you will get an error message. Make sure that you select a cell within the database to begin with.

Familiarize yourself with many variations and tests.

Step 5: Close Excel without saving anything.

Go to the next lesson
Lesson 19 : VBA for Excel Variables