Delete text in cells but keep formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have an excel grid with headers in rows and columns. Within the
cells there is data that I dont need, but I do need the formulas in those
cells, how do I clear the data in the cells without deleting the formulas?

( I have tried GO TO - Special - Constants but this clears all of my headers
rows and additional info that I need, I only want to clear the text in the
cells that currently contain formulas)
 
Hi YanYan,
See http://www.mvps.org/dmcritchie/excel/insrtrow.htm

To clear out constants in a selection range, something like one of the following::

Selection.EntireRow.SpecialCells(xlConstants).ClearContents

Selection.SpecialCells(xlConstants).ClearContents

You could protect some cells with constants by changing them to formulas
Range("A3:A3000").EntireRow.SpecialCells(xlConstants).ClearContents .

The use of SpecialCells automatically limits you to the used range.
There is a problem with with Special Cells that you might encounter in an
extreme range and one that you should be very concerned about when
deleting content. Actually the failure is more than 8192 areas within a range
Failure if more than 8192 cells selected by specialcells
see MS KB http://support.microsoft.com/?kbid=832293
and there is no undo from within a macro, so even if you see that everything
is deleted you can't undo.
 
If the text is the result of formulas, you are stuck, unless you re-write
all the formulas in the form

=IF(SomeFlag=specific value,old formula,"")

HTH,
Bernie
MS Excel MVP
 
No the text is not the result of formulas, just data that has been entered
into the cell to help describe the formula, but I dont need it because this
sheet is merging with another sheet and all that mumbojumbo that I dont need
shows up.
 
Then use Go To....- Special - Constants... but to not clear all of your headers, first select only
the cells that currently should have formulas.

HTH,
Bernie
MS Excel MVP
 
Back
Top