Let's say you have a range that you want to convert to a table
Something like this that has fills, font colors, font weight, borders and number formats.
If you convert this to a table this formatting remains.
To remove this formatting and preserve the table formatting, we can use some VBA code.
Download the Example Workbook
All of the VBA code I use in this post is in the example workbook. Download it and use it yourself.
Enter your email address below to download the workbook.
The way this works is to apply a modified Normal style to the table to clear other formats, and then reapply the table style.
I say a 'modified' Normal style because if we just apply the Normal style it will reset any number formatting.
So I'm creating a new style called MyNormal which will by default be the same as the Normal style. But I am clearing the .IncludeNumber property for the style.
This is the same as creating the new style by hand and unchecking the Number box.
With the new MyNormal style created, the code applies that to the table, then reapplies the table style that it saved earlier in the TabStyle variable.
How To Use the Code
Select any cell in the table then run the VBA.
The code checks that the active cell is in a table, if it isn't, the code will do nothing and end.
If you want to, you can have the code display a message instead, to say the active cell isn't part of a table. I'll leave that up to you.
How would you put this code if it’s looping through multiple worksheets with Tables (same format)? On the second table, I’m receiving an error: on the .Range.Style = “MyNormal” -> Run Time Error: ‘450’ – Wrong Number of Arguments or Invalid Property Assignment.
Hi Carl,
You should just need to put in a loop to work through every workbook and table. As I didn’t write this in myself I’m not sure how you have implemented it.
Can you please start a topic on the forum and attach your workbook so I can check it.
Regards
Phil
How does your macro differ from the “Apply and Clear Formatting” option available on the ribbon under Table Tools / Design / Table Styles?
Hi Peter,
I don’t see an ‘Apply and Clear Formatting’ option. In O365 I can see ‘Clear’ under Design->Table Styles but that just clears the table style.
You can go to Home->Clear-Clear Formats and that clears all formatting including number formatting.
My macro clears formatting but preserves the number formating.
Regards
Phil