Get Only The Used Areas In A Range – #Excel #VBA


Struggling To Excel

I recently published a post about automatically formatting a table in Excel using VBA. That got me thinking, how awesome it would be, if we could format all the tables in a sheet, with a single click. For that idea to work, we need to get all the used areas in a worksheet; and then use the Areas Collection to loop through the tables. We can access the Areas Collection through the Areas property of the Range object.

First, we will try to understand how the Areas Collection works. An Area as a single continuous rectangular block of cells; it could even be just a single cell. If the Range consists of multiple discontinuous rectangular blocks, each of those blocks, would be stored as separate Range Objects in the Areas Collection. The key word here is discontinuous, i.e. selecting two tables together in one big Selection does not constitute two Areas; it is still just one continuous block. In other words…

View original post 204 more words