VBA Blogs: DataBodyRange - New Frontier

This is the fifth in a series about using ListObjects to manipulate Tables within an Excel workbook in VBA.

Warning! Star Wars may have been released yesterday, but don't forget the Trekkies!

Not wanting to Troi too hard, let’s continue with Data(BodyRange)!

First let’s examine the table:

Oops! Not that one - though that is pretty indeed.

Let’s filter it down to the bare-Bones to only things released before 1990.

Back to VBA, if the information in the table is copy pasted, what would the expected result be? Let’s take a Quark at it.

After running the code, the result is as follows:

Notice how it has copied ALL the data. Yar wouldn’t expect that would you?! Computers make excellent and efficient servants, but I have no wish to serve under them.

Let’s Forge ahead and get just the filtered data. In order to do that, let’s adjust the code slightly to use the .SpecialCells method of a Range. This allows selection of a particular type of cells. Given that only visible cells from the filter is required, the xlCellTypeVisible is the property needed.

And violá!

Easy peasy, don’t need go to a Kirk and pray to the Lord for help.

For the SpecialCells methods other properties can be used as well, for example:

• xlCellTypeFormulas - Cells containing only formulae
• xlCellTypeBlanks - Empty cells
• xlCellTypeLastCell - last cell in the range

For the full list, please refer to the MSDN library sheet here.

Live long and prosper.