Excel

Clear cell formats and values

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Zack Barresse

Description:

With VBA, we can make use of the Clear method, which is not as readily available in native Excel functions. This gives us full use of the method. 

Discussion:

There may be times where you want to clear all formats and values from cells in addition to clearing the contents of the cells. You want to do it fast, but don't want to delete the cell because it will shift all of your other data, resulting in formula shifts and imbalances. With the VBA Clear method, we can clear a cell of all formatting, conditional formatting, data validation, custom formatting, values, etc, and let you start from "scratch". 

Code:

instructions for use 

			
Option Explicit 
 
Sub ClearCell() 
     
    Selection.Clear 
     'If you will be clearing the same section/cells repeatedly, you can
     '  substitute the "Selection" for "Range("A1:A10")", or your suitable
     '  range.
     
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.
  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.
  9. Press Alt + F8, select 'ClearCell', press Run.
 

Test the code:

  1. From an existing workbook, save first.
  2. Select desired range to clear.
  3. Press Alt + F8.
  4. Choose 'ClearCell'.
  5. Press 'Run'.
 

Sample File:

ClearCellEx.zip 5.83KB 

Approved by mdmackillop


This entry has been viewed 215 times.

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