Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Clearing Everything Except Formulas.
by Allen Wyatt
(last updated May 17, 2014)
Roni wants want to clear everything in a workbook except for cells which may contain formulas. This task can be completed either manually or through the use of a macro.
If you want to do the clearing manually, you can follow these steps:
Figure 1. The Go To dialog box.
Figure 2. The Go To Special dialog box.
This works great if you only need to clear out the non-formula contents of a worksheet once in a while. If you need to do it more often, then you can simply use the macro recorder to record the above steps. Or, if you prefer, you can create your own macro from scratch, such as the following one:
Sub ClearAllButFormulas() Dim wks As Worksheet For Each wks In Worksheets 'ignore errors in case there is only formulas On Error Resume Next wks.Cells.SpecialCells _ (xlCellTypeConstants, 23).ClearContents On Error GoTo 0 Next Set wks = Nothing End Sub
This macro is particularly useful if you need to clear out all the non-formula cells in an entire workbook. The reason is because it does the clearing on every worksheet in the entire workbook, without you needing to do the clearing manually.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3226) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Clearing Everything Except Formulas.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you want to get rid of the contents of a range of cells, a quick way to do it is with the Fill handle. Yes, you can use ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.