Excel

Remove all cell formating and styles, and restore default font

Ease of Use

Easy

Version tested with

97, 2003 

Submitted by:

Ken Puls

Description:

This macro will clear all the formats from the selected cells, remove all styles, and restore all fonts to the font that the workbook was created in. Specifically, it will remove bold, italics, underlines, strikethrough, subscript, superscript, font color, cell color, cell borders, number styles, etc... An option is also included to reset the font to your Excel default font (as defined in Tools|Options|General) 

Discussion:

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 also resets all fonts and font sizes to the workbook defaults. If you need code which does not reset those aspects, please see KB entry http://www.vbaexpress.com/kb/submitcode.php?kb_id=343. 

Code:

instructions for use 

			
Option Explicit 
 
Sub RemoveFormatsAndRestoreFont() 
     'Remove all formatting except changes in font and font size
     
     'Turn off screen updates to improve performance
    Application.ScreenUpdating = False 
     
     'Clear formatting
    Selection.ClearFormats 
     
     'If you would like to restore the font to the application default as defined in
     'Tools|Options|General, remove the ' from the beginning of the line below
     'Selection.Font.Name = Application.StandardFont
     
     '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. If you want to restore to the application's default font, remove the ' from the line noted in the middle of the code.
  8. Press Alt + Q to close the VBE.
  9. 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, and that the default font type and size have been restored as desired.
 

Sample File:

ResetToDefaultFont.zip 9.21KB 

Approved by mdmackillop


This entry has been viewed 189 times.

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