removing values not links

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

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.
---
Regards,
Norman



"Jase" <[email protected]> wrote in message
news:[email protected]...
> 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