MrExcel Publishing
Your One Stop for Excel Tips & Solutions

macro to delete data preserve formulas


Posted by Eric on November 16, 2001 6:52 AM

Protected Workbook has columns with cells for data entry and cells containing formulas interspersed. Cells for data entry have colored fill to help guide user. Cells with formulas are locked. Would like a macro which will select the column (regardless of which cell within the column is active when macro is run), erase any data entered (leaving formulas intact), and return to the cell which was originally active.
Any help will be appreciated.


Posted by Catherine Munro on November 16, 2001 10:21 AM

ActiveCell.EntireColumn.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

ActiveCell.EntireColumn.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
ActiveCell.Select

Assumes the only data you want to clear is number constants; if you have text to clear as well, and need to discriminate data entry cells from labels, you may need to get fancier, but let me know if this works for you.

Posted by Eric on November 16, 2001 11:24 AM

Very Nice! Thanks, Catherine NT