Forum Home
Press F1
Thread ID: 98667	2009-04-02 04:41:00	Excel 2003 Delete contents	Happy Harry (321)	Press F1

Post ID	Timestamp	Content	User
761764	2009-04-02 04:41:00	Hi One and all For the start of the new financial year, I wish to be able to clear "just" the data from a workbook consisting of 13 sheets, 12 months and a yearly sheet. Over the last year I have modified and remodified and have got this workbook and formulaes and formatting, exaclty as I want it. Can anyone tell me how to only delete the data from the 13 worksheets and leave everything else intact. I have already made multiple backups in case it all turns to custard but hope someone can save me some time. Cheers HH	Happy Harry (321)
761765	2009-04-02 06:21:00	office.microsoft.com	Sweep (90)
761766	2009-04-04 01:32:00	office.microsoft.com Thanks Sweep Yes I had found that article but, it doesnt do what it says. If I select the block of cells and delete contents, it also deletes the formulas. I have found a work around by selecting the blocks of cells that contain entered data (but no formula) & deleting the contents. This then clears the contents of the cells with formula in them, but does not clear the formulas from these cells. This takes a little more work but achieves the required result. Thanks for the input HH	Happy Harry (321)
761767	2009-04-04 03:02:00	T I have found a work around by selecting the blocks of cells that contain entered data (but no formula) & deleting the contents. This then clears the contents of the cells with formula in them, but does not clear the formulas from these cells. Well thats what I do. Data isn't entered into formula cells so they shouldn't be touched anyway.	pctek (84)
761768	2009-04-04 03:18:00	My initial thoughts would be to run a check through vba, where it deletes the contents of any cell where the first character is =	the_bogan (9949)
761769	2009-04-04 07:58:00	Hi, if I understand you correctly you want to clear the contents of constants (non-formula cells) and retain the formulas. A macro can do this but the trouble is you will often have some constant values you want to keep such as header rows etc so requires some logic to determine which constants to keep. You can however quickly select all constants in a sheet through Excel's GoTo feature as follows: 1) Select the target rows (ie exclude the header row/s) 2) Select Edit | GoTo from the menu 3) Click the Special button 4) Choose the Constants option then click OK. If you do this regularly and want a macro let me know. regards, Graham	Parry (5696)
761770	2009-04-04 08:06:00	Good to see you back Parry. A macro will work but the the OP only needs to clear some cells once a year. If I knew which cells I could help but I definitely believe some help is needed. I can't see the spreadsheet yet. I normally hide constants outside the visible area and also not in the print area.	Sweep (90)
761771	2009-04-04 21:36:00	Hi Sweep, if it's only once a year then this is over the top but may be of use. The macro below will clear contents of constants where the cell is unlocked. Cells can be unlocked by selecting Format | Cells | Protection tab then unchecking the Locked box. If you have protected the sheet the macro will ask for the password and then do its thing and reprotect the sheet again. To create the macro press ALT+F11 then Insert | Module and paste the code into the right hand window then ALT+Q to return to Excel Sub ClearConstants() 'This macro will clear the contents of constant values that are unlocked Dim Sht As Worksheet, c As Range, Rng As Range, RetVal As Variant, IsProtected As Boolean 'loop through each sheet in the workbook For Each Sht In ActiveWorkbook.Sheets 'Set a default value for sheet protection IsProtected = False 'Check to see if sheet is protected. SpecialCells requires sheet to be unprotected. If Sht.ProtectContents = True Then IsProtected = True 'set variable so we can reprotect sheet when finished RetVal = Application.InputBox("Please enter the password for sheet " & Sht.Name, "Macro Needs To Unprotect Sheet") 'Attempt to unprotect the sheet Sht.Unprotect RetVal 'Check if protection worked If Sht.ProtectContents = True Then MsgBox "Unable to unprotect the sheet. Please unprotect the sheet/s manually", vbCritical, "Failed To Unprotect Sheet" Exit Sub End If End If 'Set the range of representing all cells with constants. Cells with formulas will be excluded. On Error Resume Next 'next line will fail if there are no constant cells Set Rng = Sht.Cells.SpecialCells(xlCellTypeConstants) 'SpecialCells will only work if sheet is unprotected If Err.Number <> 0 Then 'An error occured so this is a blank sheet so go to the next sheet GoTo SkipSheet End If On Error GoTo 0 'Loop through each cell For Each c In Rng 'If cell was locked it would have been protected so only clear contents of unlocked cells If c.Locked = False Then c.ClearContents End If Next c SkipSheet: 'If sheet was protected then reprotect sheet If IsProtected = True Then Sht.Protect RetVal End If Next Sht End Sub	Parry (5696)
1