Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete text in cells but keep formula
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete text in cells but keep formula
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).Clea rContents Selection.SpecialCells(xlConstants).ClearContents You could protect some cells with constants by changing them to formulas Range("A3:A3000").EntireRow.SpecialCells(xlConstan ts).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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "YanYan" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete text in cells but keep formula
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 "YanYan" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete text in cells but keep formula
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. "Bernie Deitrick" wrote: 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 "YanYan" wrote in message ... 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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Delete text in cells but keep formula
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 "YanYan" wrote in message ... 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. "Bernie Deitrick" wrote: 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 "YanYan" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I concatenate text in cells to make a working formula? | Excel Discussion (Misc queries) | |||
Formula to strip figures from cells text strings | Excel Discussion (Misc queries) | |||
format cells having text | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |