Clear Formatting From Cells Macro

Hello, Excellers. Welcome back to another #MacroMonday #Exceltips blog post in my 2021 series. Want a very fast way to remove formatting from a range of cells then this is the macro you need. This Excel macro clear formatting from a user-selected range of cells with the click of a mouse. Great. Let’s get coding.

Read All of my Macro Monday Blog Posts

Prepare To Write The Macro.

First, open the Visual Basic Editor. There are two ways to do this.

Hit ALT +F11 or
Select the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

So, if you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.

Next, you need to decide where to save your Excel macro. You can either save the code in your current workbook or in your Personal Macro Workbook. If you want to reuse the macro over and over again (like this one) in many workbooks save it in the Personal macro Workbook. If the code is only to be used in the current workbook, save it there.

Here Is The Code.

Sub ClearFormatting()
  Selection.Clearformats
End Sub

Step 1. Insert A New Module.

The first step is to insert a new module into your Personal Macro Workbook.

Just hit the Insert Menu then Module.

After the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro ClearFormatting(). You will see that Excel automatically inserts the End Sub ending code once we create a new macro. Any other lines of code are placed in between these two.

Step 2. Use the Clear.Formats Method. (Clear cell formatting).

The second line of code does all of the work for us. Using the Clear.Formats method, all formatting is removed from the selected range of cells.

Step 3. End the Macro.

Finally, once all of the cell formattings in the selection of cells is clear the code ends. The last line of code, ‘End Sub’ part of the code was inserted automatically when the macro was named. So, as I have saved this Excel macro in my Personal Macro Workbook, I can reuse this code over and over again.

How To Excel At Excel