clear contents but not formulas

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,776
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?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
534
Office Version
  1. 2013
Platform
  1. Windows
Try
Code:
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

Banned
Joined
Apr 11, 2006
Messages
2,328
If you just want to clear all non-formula numbers from the entire worksheet :-

Code:
Cells.SpecialCells(xlCellTypeConstants, 1).ClearContents

Note : this will also clear non-formula dates.
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,776
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

Banned
Joined
Apr 11, 2006
Messages
2,328
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 :-

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top