Clear Constants from Selected Cells

Location: http://www.mvps.org/dmcritchie/excel/clear.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

Opening comments

Here is a simple start on writing a macro to clear the cells with constants in the selected area, leaving the cells with formulas intact.

To preserve some constants you might need to make them formulas.
i.e.     ="constant"

Manually clearing constants

Creating and implementing a macro, to clear constants

You can record a macro that does what you did manually.  It might look something like the following.
Sub Macro1()
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
End Sub

You can combine the two statements into one statement.

    Selection.SpecialCells(xlCellTypeConstants, 23).ClearContents
But that still leaves a couple of problems.  Running the macro again will produce an error since nothing can be found; and the other serious problem is that if you select a single cell the Current Area will be changed instead of just one cell.

Fixing those things our macro might look like the following.

Sub ClearConstants()
' Documented:  http://www.mvps.org/dmcritchie/excel/clear.htm
' 2001-06-20 misc, Re: Clearing large numbers of inputted values
    On Error Resume Next
    Err.number = 0
    Intersect(Selection, _
       Selection.SpecialCells(xlCellTypeConstants, 23)).Clear
    If Err.number <> 0 Then
       MsgBox Err.number & " " & Err.Description & Chr(10) & err.description
    end if

End Sub
In the above INTERSECT(Selection, Selection(...  prevents the selection from expanding beyond the used range.  See extensive notations on my Proper page, for more information.

The "On Error Resume Next", resumes with the next statement if there is an error.  You probably do not want to have this code active when you are testing your code.  By placing addintional statements in the code, we can see what the error actually is if there is an error.  Because there is only one statement in the subroutine it is probably not necessary to include turning off screen updating and turning off calculation.  (see slowresp.htm)

You can use numbers like 23 or constants like xlNumbers.

 On Error Resume Next
 myRange.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
 On Error Goto 0

Assigning an icon on your Toolbar to invoke the macro

Related

This page serves as a simple introduction to each of the following pages.

You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on June 20, 2001. 

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved