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.
- Select range of cells you want to remove constants from
- Edit, GoTo, [Special], Constants (and each type: numbers, text, logicals, errors))
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).ClearContentsBut 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 SubIn 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 additional 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
////// test and insert code from David Unger 2016-06-01 -- 2016-06-02 concerning large files////
- Right-click on the toolbar area (same as tools, customize)
- On the toolbars tab of customize: Press [New] button, and name the toolbar "Custom 1" is fine. Move it to your toolbars area so that it is docked at the top with the rest of your toolbars.
- On the contents tab of customize: Choose macros under category on the left, and drag "custom button" with the smiley fact to your "custom 1" toolbar.
- Right-Click on your button, you may have to return here after doing each of the following:
- Name: (supply a name) in this case "Clear Constants"
- Assign a macro name: "personal.xls!ClearConstants"
- Change button image: grey surface will do until you make up one
- You can edit the button to change the pixels or you can copy any image to it that will become square by distortion, if necessary, and reduced to 16 bits by 16 bits. More information on my Toolbars page. One method would be to place a "C" in a cell with a pink font and white background, make it bold and square up the cell, copy (ctrl+c), then
back to Right-Click on the button in Customize and use "Paste Button image" then use "edit button image" and draw 3 diagonal lines through the icon image (1 black diagonal up from left, 2 black diagonals down from left).
- Surround with a 1 pixel gray border, so the button with a white background stands out on web page for illustration. In Excel it wouldn't make any difference as the toolbar is grey.
This page serves as a simple introduction to each of the following pages.
- Range.SpecialCells Method (Excel), values of XlCellType constants (XL 2013 and later)
- Formula page has instructions for installing and using a macro or a User Defined Function (UDF). The page also describes creating and using macros to show the formula and to show the formats of other cells on the worksheet (GetFormula, and GetFormat). Under GetFormat are quite a number of examples on how to use Custom Formatting.
- Insert Rows and copy formulas, retains formulas by deleting constants in the inserted rows.
- Proper, and other Text changes has code for macros to convert selection to Proper case (title case), lowercase, or uppercase. There is a lot more than just the code to effect the change. Suggest you read this page carefully.
- Slow Response has a lot of suggestions for making your code run faster. As with all of my material look in the Related area for additional information on my site or elsewhere.
- Tool Bars, describes how to add to your menus and toolbars. (mainly toolbars)
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