Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Close Range

1 December 2017

Warning! This was written whilst listening to a New Order playlist and Adele is making a comeback from last week to show the various parts of a table.

Last time, experienced the “Ecstasy” of being able to refer to a Table, but now it is time to play with its components “All Day Long”. 

This article will specifically explore the “Primitive Notion” of the Ranges. The Range property covers the entire Table and all the properties that apply to Ranges apply to it.

It’s “Fine Time” that the whole table is considered.

What is the “Vanishing Point” of a table?

By using the Range.Delete method, the whole table can be removed in one fell step.

After a quick “Run” of that script, “Everyone Everywhere” would not be able to find trace of the table.

But there is a more direct way to completely remove a table. It might be “Academic” but works just as well and a lot less ambiguous – delete the ListObject directly!

However before you can express any “Regret” over losing data and “Run Wild” deleting ListObjects, what if the table structure is to be removed but not the actual data itself? How to convert from Table to a Range, as if the Convert to Range button were clicked on the Ribbon?

Take a “Turn” with the Unlist method of ListObject.

Before:

After:

“Hey Now What You Doing?” The Table structure has been successfully converted and all filters have been removed yet the formatting has remained the same

Newsletter