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, we have to get rid of the blank cells in between the two tables, to make them two distinct Areas.
Here is how we can get only the used areas in a Range:
- First, we get all the cells that either have formulas, or a values, and store them in a Range Object.
- Every table is bound to have blank cells, and we have to include them too. Therefore, we loop through all the Areas in the Range we created in Step 1, fetch its CurrentRegion, and accumulate the cells in another Range Object.
- Return the Range object that we created in Step 2.
Here is the Code
'#####################################################################
'UsedAreas : Function that returns only the used reagions in a range
'If called without any arguments, it will return the used regions in
'the ActiveSheet.
'#####################################################################
'Author : Ejaz Ahmed
'Email : StrugglingToExcel@outlook.com
'Date : 15 June 2014
'Website : http://strugglingtoexcel.wordpress.com/
'#####################################################################
Function UsedAreas(Optional ByRef WhichRange As Range) _
As Range
'Declare Runction level Variables and Objects
Dim ConstantsRange As Range 'Stores all the cells that have values
Dim FormulaRange As Range 'Stores all the cells that have formula
Dim UsedRange As Range 'Stores Used Area
Dim ContentRange As Range 'Includes Used Area's CurrentRegion
Dim EachArea As Range 'Used in the Loop
'If the user did not pass any range to the function, use the
'sheet's used range.
If WhichRange Is Nothing Then
Set WhichRange = Application.ActiveSheet.UsedRange
End If
'The SpecialCells Method includes the entire sheet's UsedRange
'if called from a single cell. So only proceed if the user selected
'more than one cell
If WhichRange.Count > 1 Then
'Ignore the errors produced if there are no cells with Formula
'or Constants
On Error Resume Next
Set ConstantsRange = WhichRange.SpecialCells(xlCellTypeConstants)
Set FormulaRange = WhichRange.SpecialCells(xlCellTypeFormulas)
Err.Clear
On Error GoTo 0
'Combine both the Ranges together
If Not ConstantsRange Is Nothing Then
Set UsedRange = ConstantsRange
End If
If Not FormulaRange Is Nothing Then
If UsedRange Is Nothing Then
Set UsedRange = FormulaRange
Else
Set UsedRange = Application.Union( _
UsedRange, FormulaRange)
End If
End If
'We dont need these ranges anymore, forget them to save memory
Set FormulaRange = Nothing
Set ContentRange = Nothing
'We already have all the cells that have stuff in them, but there
'may be blank cells that are actually part of a table, but do not
'contain data. Therefore, we loop through the areas and include the
'current regions
If Not UsedRange Is Nothing Then
'Set the Final Range to the first Area, so we dont have to check
'if it is not empty later in the loop
Set ContentRange = UsedRange.Cells(1, 1).CurrentRegion
For Each EachArea In UsedRange.Areas
'Check if the Area is already in the Final Range
If Application.Intersect(EachArea, ContentRange) _
Is Nothing Then
'Include its current region if it is not already in the
'final range
Set ContentRange = Application.Union( _
ContentRange, EachArea.CurrentRegion)
End If
Next EachArea
End If
End If
'If the selection had used areas, return it, or
'just return the Range that was passed to the function
If ContentRange Is Nothing Then
Set UsedAreas = WhichRange
Else
Set UsedAreas = ContentRange
End If
End Function
A Cover Macro to loop through all the areas:
Sub SampleSub()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim EachArea As Range
Dim UsedAreasRange As Range
Set UsedAreasRange = UsedAreas()
'Select the Range if you choose to
UsedAreasRange.Select
For Each EachArea In UsedAreasRange.Areas
'Your Code here
Next EachArea
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Download
Test the format-all-tables macros. I have four cover macros in there:
- Format all the tables Gold (CTRL + SHIFT + Q)
- Format all the tables Crimson (CTRL + SHIFT + W)
- Reset all formatting (CTRL + SHIFT + E)
- Just Select the used Areas (CTRL + SHIFT + R)
Hold the CTRL and SHIFT buttons, go bananas with “Q” and “W”, and watch the magic!
The code formats about 100 tables in seconds. Next time your boss asks you to change formats for a 100 tables, What would you say?
Reblogged this on SutoCom Solutions.
Pingback: [excel] Here’s how to count number of same colored cells | A Project Manager
Pingback: Unknown number of dynamic arrays - how to?
Ejaz, using your macro, can I specify a used area? For example, I’m able to extract conditional formatting parameters onto a sheet; from there, I’d like to use the last area as the range to programmatically apply CF. How would I specifiy which area I’d like to use??
Thanks again!
You can loop through the areas collection. Use a for each or a for loop. Keep track of the row number of the first or last row in each area to determine which is the last area.
If I had to do this, I’d keep track of the address of an area in a string variable, by comparing its row number with a maximum-row-number that I would keep track of in a long variable. Every time, the row of an area in the loop, is more than the max row, I update the range address string, and the max row long variable with that area’s details.
At the end of the loop, you’d end up with the last area’s address in the string variable.
Right after I posted (why does inspiration always occur AFTER you hit Send??), I found that the code below worked. Is there any bad juju to doing it this way?? Thanks.
Set rngCFSource = rng_UsedAreas(rngCFSource) ‘ ~~ Find each individual CF output
Set rngCFSource = rngCFSource.Areas(rngCFSource.Areas.Count) ‘ ~~ Select LAST UsedArea on sht (most recent CF output)
That relies on excel arranging the areas by itself. To be very honest, I do not know for sure if it does that.
The round about method that I broke fingers typing out on my phone checks for the last are in terms of row number.
You can easily test it out though. Set a range object like this:
Set TestRange = Range(“A10:A11,A5:A7,A1:A2”)
And then try to print its address, and see if excel rearranged the ranges.
You’re right – it does NOT re-arrange the range. I guess for my setup (same columns, no deviation of data location, no specific range designation) this would work but for anything that’s more free-form (or where the range is explicitly specified), my “solution” would give the bottom range listed rather than the last range on the sheet.
The other issue with my idea is that it reads from top to bottom and may miss if the last range is the range at the far right and not the bottom range.
Thanks again. Hope you find splints for your fingers!!
How thoughtful of you! My fingers feel much better already.
Thanks for testing. If I write a post about it, I’d include you in the credits!
No worries! I’m just happy to get such prompt and helpful insights. Thanks again for your great programming! I use it everyday.
I am glad you use the stuff on my blog. Good to hear that from a reader.
Ejaz, I have two (more) questions about how to adapt your function;
1) I’m curious if there’s any way to limit each area to visible cells only? I tried re-defining [Set ConstantsRange = ConstantsRange.SpecialCells(xlCellTypeVisible)], but that doesn’t work at all. Is there some other way to limit the either the range or the area to visible cells only?
2) Is there a way to specify which areas to keep? I have multiple areas and I only want to keep the first two; is that possible??
Again, thanks so much.
I am very happy that you are using this function regularly!
You are thinking along the right lines with regards to the visible cells problem. You have to use the special cells method, but at a different point in the code. You wouldn’t even have to modify my function. All you have to do is use it as it is in one of your subs, and in the next line, just use the special cells method. The reason why doing it in the beginning does not work, is because I use the Current Region property to account for blank cells in a table, and it does not care whether a cell is visible or not. Therefore, even if you did start with visible cells, the current region property will include adjacent cells even when they are hidden.
Now let us try to address the second problem that you have. You can use the FOR EACH loop, or even just a FOR loop to loop through areas. You can add a few IF THEN statements there to determine which areas to keep and store them in another range object. Look into the UNION function.
Excel does not order areas according to row and column numbers. It remembers areas in the order they were added to the range object. And in instances like these, you can never be sure of the order. The first two areas need not be the top most or the left most area in your “Selection”. You would have to account for that all by yourself in your code.
ALTERNATIVE: Hide the areas you don’t want to keep, temporarily, and use my answer to the first question; take down two birds with a stone!
Looks like we have already discussed the Areas Order problem. I am sorry I made you sit through that again.