VBA Chapter 17 of 24: VBA Excel to work with Databases

This chapter is about working with the database functionalities of Excel. Chapter 21 is about VBA, external databases and SQL

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.

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

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

Next Chapter: VBA for Excel Code for Variables

VBA Table of Contents
or use the quick links below

Discover Even More in 50 Excel spreadsheets

613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

VBA Excel to work with Databases