Macro to clear data but keep formula

[Ryanchris]

Hello all!
I have some data on a sheet called "NewParts" and often need to delete the range N1:N24.

Using something like Range("N4:N24").ClearContents, clears everything in those cells including the formula that I have in those cells. Any ideas on how to clear just the numbers in those cells?

Thank you so much in advance for your help!!

[大灰狼1976]

Hi Ryanchris!
Something like below:

Sub test()
Dim rng As Range
Set rng = [n4:n24].SpecialCells(xlCellTypeConstants, 23)
If Not rng Is Nothing Then rng.ClearContents
End Sub

--Okami

[Paul_Hossler]

I'm assuming that in N4:N24 there are constants as well as formulas

If they are ALL formulas they you need to clear the data that the formulas use

Otherwise something like



On Error Resume Next ' in case there aren't any

Range("N4:N24").SpecialCells (xlCelltypeConstants).ClearContents

On Error Goto 0