VBA Express : Excel - Remove all cell formatting

Excel

Remove all cell formatting - Retaining fonts

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. 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.

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 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.

Code:

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:

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

Test the code:

Highlight a range with formatted cells in it.
Press Alt + F8 to display the Macro dialog box.
Choose RemoveFormats and click Okay.
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 198 times.