removing values not links

[Guest]

Hi All

I have a worksheet with around 5000 rows and 10 columns. Held within these
values are formulas and values. I am trying to get some code that removes
all of the values but does not touch the formula's.

I have been able to do this with a big loop, but this takes soooo long it is
ridiculous. I was wondering if anyone else has come up with a more efficent
way of accomplishing this task.

Thanks heaps
Jase

 

[Norman Jones]

Hi Jase,

Try:

Sub Tester04()
Dim col As Range
Dim Rng As Range
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

ActiveSheet.UsedRange
For Each col In ActiveSheet.UsedRange.Columns
On Error Resume Next
Set Rng = Intersect(col.SpecialCells(xlConstants), col)
On Error GoTo 0
If Not Rng Is Nothing Then Rng.ClearContents
Next col

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

Each column is processed sequentially to avoid known (8192 areas) problems
with large SpecialCells ranges.