Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 2-8: VBA Excel to work with Databases
(Levels: Advanced)

To really get the most out of VBA working with databases you must master these functionalities in Excel. Visit the website on Excel and study the sections on databases and database functionalities.

In alphabetical order here are the 7 VBA words that you need to learn to work efficiently with Excel databases:

AdvancedFilter, AutoFilter, AutoFilterMode, CurrentRegion, FilterMode, ShowAllData, Sort

When you work in an Excel database you must first make sure that all filters are off. To this end you will start your procedure with these two "IF" statements. First select any cell within the database.

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

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

Knowing that a database in a set of connected rows and columns you can select it all with:

Range("A3").Select
Selection.CurrentRegion.Select

Once this is done, you can count the number of rows (records) and the number of columns (fields) with the following code:

varNbRows=Selection.Rows.Count
varNbColumns=Selection.Columns.Count

In fact the number of records is the number of rows minus one (the title row) and here is the code:

varNbRecords=Selection.Rows.Count - 1

I never write the code for filtering (advanced or autofilter) a set of data I use the macro recorder and then modify the code.

Sorting Data with VBA

Create a set of data with 5 fields (columns) and a few records (5). On row 1  are  the title cells  and row 2  is the first  many records. The MC is only filtering the first record because I have not selected them all. To correct this situation, I modify the code to this

There is one piece of code that the Macro Recorder can write for you to sort the  data. The MR will write something like this:

Range("C3").Select
        Range("A1:E2").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
                xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal

Range("C3").Select
        Selection.CurrentRegion.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:= _
                xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal

I could also manually change the sorting order and I always remove the argument DataOption1:=xlSortNormal including the preceding comma because older versions of Excel will bug on it. So the final code is:

Range("C3").Select
        Selection.CurrentRegion.Sort Key1:=Range("C3"), Order1:=xlDescending, Header:= _
                xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Note: The Space/Underscore allows you to break a long sentence and continue it on the next line.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple