Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel should let me clear cell contents without clearing formulas
Excel allows you to clear cell contents but it also clears formulas. We
sometimes need to clear cells to produce mockup copies of financial statements that need to be blank in order to enter new numbers from year to year. Too much time has to be spent in determing which cells have formulas (which we want to keep) and which do not. We need another option to accomplish this. We use Microsoft Office Excel 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel should let me clear cell contents without clearing formulas
Use this
Select your data F5 SpecialConstants OK Delete See also the VBA help for SpecialCells -- Regards Ron de Bruin http://www.rondebruin.nl "BJM" wrote in message ... Excel allows you to clear cell contents but it also clears formulas. We sometimes need to clear cells to produce mockup copies of financial statements that need to be blank in order to enter new numbers from year to year. Too much time has to be spent in determing which cells have formulas (which we want to keep) and which do not. We need another option to accomplish this. We use Microsoft Office Excel 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel should let me clear cell contents without clearing formulas
You already have it.
Select the area you want to analyze and do Edit=goto Special and select constants (add further restrictions if you just want to clear numbers or just text or just text and numbers) then do Edit clear contents. -- Regards, Tom Ogilvy "BJM" wrote in message ... Excel allows you to clear cell contents but it also clears formulas. We sometimes need to clear cells to produce mockup copies of financial statements that need to be blank in order to enter new numbers from year to year. Too much time has to be spent in determing which cells have formulas (which we want to keep) and which do not. We need another option to accomplish this. We use Microsoft Office Excel 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel should let me clear cell contents without clearing formu
Here's a thought too
After clearing entries save it as a template and you always have a clean sheel to start from. Lou "Tom Ogilvy" wrote: You already have it. Select the area you want to analyze and do Edit=goto Special and select constants (add further restrictions if you just want to clear numbers or just text or just text and numbers) then do Edit clear contents. -- Regards, Tom Ogilvy "BJM" wrote in message ... Excel allows you to clear cell contents but it also clears formulas. We sometimes need to clear cells to produce mockup copies of financial statements that need to be blank in order to enter new numbers from year to year. Too much time has to be spent in determing which cells have formulas (which we want to keep) and which do not. We need another option to accomplish this. We use Microsoft Office Excel 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel should let me clear cell contents without clearing formulas
Since this is the programming newsgroup the posting about Excel not providing
functionality is out of place, becase you can certainly program this action. It would be probably be nice to have a "Clear Constants" item on the the context menu just like there is a "Clear Contents" item and that like "Clear Contents" will only affect one cell if only cell is selected. I've not seen Microsoft add additional function to an existing version, so it would be a long time before you would see any change, meanwhile, ... Both Excel and VBA allow you to clear content of non formulas, and since this is the programming group, you can make up a toolbar button, menu, or context menu (right click menu) to make it easier with a macro... Excel:: Select an area Ctrl+G (Edit, GoTo), Special, Constants (with Numbers, Text, Logicals, Errors) Del key (or Edit, Clear Contents) VBA: Selection.EntireRow.SpecialCells(xlConstants).Clea rContents or Selection.SpecialCells(xlConstants).ClearContents Sub Clear_Constants() 'D.McRitchie 2005-11-26 rightclick.htm - insrtrow.htm Dim rng As Range 'prevent expansion of a single cell selection Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants)) If rng Is Nothing Then MsgBox "No constants in selection area(s) -- no removal" Else rng.ClearContents End If End Sub Installation for use in context menu (rightclick) requires additions to cell, column, and row CommandBar controls. More information in http://www.mvps.org/dmcritchie/excel...lear_constants More information in http://www.mvps.org/dmcritchie/excel/rightclick.htm --- 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 "BJM" wrote in message ... Excel allows you to clear cell contents but it also clears formulas. We sometimes need to clear cells to produce mockup copies of financial statements that need to be blank in order to enter new numbers from year to year. Too much time has to be spent in determing which cells have formulas (which we want to keep) and which do not. We need another option to accomplish this. We use Microsoft Office Excel 2003. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...el.programming |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel should let me clear cell contents without clearing formulas
date in comment s/b 2005-11-19
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy an Excel worksheet with formulas and clear contents. | Excel Discussion (Misc queries) | |||
Clear a worksheet without clearing formulas? | Excel Discussion (Misc queries) | |||
excel command to clear contents except 2 formulas | Excel Discussion (Misc queries) | |||
Clear contents but leave formulas in Excel | New Users to Excel | |||
How do I clear a column of data without clearing the formulas? | Excel Discussion (Misc queries) |