Remove Formatting from a Table

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.

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.