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