My Excel Journey: How to Clear Cell Formatting in Excel VBA

In this blog post, you will learn about how to remove multiple formatting from our dataset.

Sometimes we face the situation when we have a dataset with unwanted formatting.

In the below dataset, we applied some formatting in-text range (“A1: A10”), such as Bold Font, Comments, Hyperlink, Bold, Italic, and Underline with a color background.

Now with the help of VBA, we will remove these formatting one by one.

So for that:-

Go to Developer Tab and click on Visual Basic and click on Insert and open a new Module and start writing the code.

In the below image I have written some format removal codes.

I will run these lines codes one by one and the formatting will start removing or clear as per the code.

1. Clear comments

See in the below image I have executed the “Clear Comments” code and comments have been removed from the dataset.

2. Clear Hyperlinks

As you can see that our next line color has been turned yellow which means, this line of code will be executed now.

This is the sign, which line is being executed.

See in the dataset, we have “Hyperlinks” in range (“A5: A7”), light blue color text.

Our next code with remove hyperlink from the dataset.

See “Hyperlinks” has been removed but the formatting is still existing.

3. Clear Formats

There are multiple formats in this dataset, executing this line of code will remove all the formattings. But our dataset will remain there without any formatting.

4. Clear Contents

Clear Contents will remove all the existing contents within the range. If the dataset contains any formatting, it will remain the same, i.e. this line of code will nothing to do with it.

To test this line of code, I add some formatting in this dataset, like background-color, bold font, Italic and underline, etc.

Now I will execute the code for “Clear Contents” and see what happens.

See in the above image, the contents with the range (“A1: A10”) has been removed but all the formatting still remain including the comments.

The font was Bold, Italic and Underline in this range, now if we again write anything within this range, the text will automatically change to Bold, Italic, or underline because formatting still existing in this range.

Just try this.

5. Clear

The last line of code is “Clear”.

Running this line of code “Clear” means, removing everything.

Now again I have applied multiple formatting within the dataset to test this line of code.

See image below:-

Now I will execute the last line of code “Clear” and see that everything like- formatting and contents including comments or background color will be removed quickly.