clear contents but not formulas

[ajm]

does anybody know how to do this? can it be done? i have a template that i am creating a "Next Year" macro for. this macro will roll all the dates in the workbook forward to the next financial year and will clear all content from the different sheets. It really want to just clear the used range but for the formulas. the only way i know is to select each section, clear that, then move on to the next section, clear that, and so on. big problem is that the number of records is dynamic, and has subtotal formulas at the end of the different sections down the page (as well as the calculations across the page). is there any simple way to do this without trying to create named ranges for everything?

 

[dcardno]

Try

Sub ClearVal()

Dim cll        As Range

For Each cll In Selection
    If IsNumeric(cll) And cll.HasFormula = False Then
        cll.ClearContents
    End If
Next cll

End Sub

This will delete values entered as numbers (but not entered as formulas, like "=15" or +15+12.3) but will leave the formulas and labels intact.

NB - the approach of selecting the required range and then deleting within it is very slow, but is used for illustration: almost undoubtedly you can determine the relevant ranges at run time (or even at compile time) and then run the routine against them without selecting them. This will be significantly faster than selecting each range in turn and running the ClearVal routine...
In addition, for any substantial bit of coding, you should probably turn off ScreenUpdating before it starts, and then turn it back on again when you are done; this will also speed things up, since Excel is not writing to the video card after every change in the sheet.

 

[Boller]

If you just want to clear all non-formula numbers from the entire worksheet :-

Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents

Note : this will also clear non-formula dates.

 

[ajm]

thanks peoples. I think i will be able to adapt dean's routine to best effect. a;though boller, you bring up a good point - dates. will have to add some "=" signs before the ones i have in the spreadsheets.

cheers, and thanks again.

 

[Boller]

thanks peoples. I think i will be able to adapt dean's routine to best effect. a;though boller, you bring up a good point - dates. will have to add some "=" signs before the ones i have in the spreadsheets.

cheers, and thanks again.

Why would you want to loop through each cell when you can do it with one line of code?

If you want to do it for selected ranges :-

Selection.SpecialCells(xlCellTypeConstants, 1).ClearContents

Re dates, you mentioned that you want to update the dates.
Why not have the macro do this? - it is a separate step from clearing the number constants.

 

[ajm]

good point.