Remove all cell formatting - Retaining fonts

Ease of Use


Version tested with

97, 2003 

Submitted by:

Ken Puls


This macro will clear all the formats from the selected cells. Specifically, it will remove bold, italics, underlines, strikethrough, subscript, superscript, font color, cell color and cell borders. It will not, however, change the font itself, or the font size in the range. 


This macro makes it very easy to remove all cell formatting with very little effort. Selecting the range and running this macro is far faster than selecting the range and manually unselecting each type of format. This macro does not change any fonts or font sizes. If you need to reset those to the defaults as well, please see KB entry http://www.vbaexpress.com/kb/submitcode.php?kb_id=345. 


instructions for use


Option Explicit Sub RemoveFormats() 'Remove all formatting except changes in font and font size 'Turn off screen updates to improve performance Application.ScreenUpdating = False With Selection 'Remove cell colors .Interior.ColorIndex = xlNone 'Remove all cell borders .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone .Borders(xlEdgeLeft).LineStyle = xlNone .Borders(xlEdgeTop).LineStyle = xlNone .Borders(xlEdgeBottom).LineStyle = xlNone .Borders(xlEdgeRight).LineStyle = xlNone .Borders(xlInsideVertical).LineStyle = xlNone .Borders(xlInsideHorizontal).LineStyle = xlNone 'Remove all special font properties and formatting With .Font .FontStyle = "Regular" .Strikethrough = False .Superscript = False .Subscript = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End With 'Restore screen updates to display changes Application.ScreenUpdating = True End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Press Alt + Q to close the VBE.
  8. Save workbook before any other changes.

Test the code:

  1. Highlight a range with formatted cells in it.
  2. Press Alt + F8 to display the Macro dialog box.
  3. Choose RemoveFormats and click Okay.
  4. Verify that the formatting in the selected range has been removed.

Sample File:

RemoveFormats.zip 7.56KB 

Approved by mdmackillop

This entry has been viewed 194 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express