Clear formulae

S

Stuart

Is there a simple way to clear formulae from the activesheet, leaving
just the values in the cells ..... or do I have to do this cell by cell eg:
setValue = ActiveCell.Value
ActiveCell = setValue

Regards.
 
F

Frank Kabel

Hi
try
activecell.value=activecell.value

or something like
range("A1:A10").value=Range("A1:A10").value
 
M

Mike

Stuart said:
Is there a simple way to clear formulae from the activesheet, leaving
just the values in the cells ..... or do I have to do this cell by cell eg:
setValue = ActiveCell.Value
ActiveCell = setValue

Regards.
Click to expand...


Stuart,

From Excel you could Copy - Paste Special - Values. One way to do
this with code is:

Sub RemoveFormulas()
With ActiveSheet.UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub



Hope this helps,

Mike
 
T

Trevor Shuttleworth

Stuart

one way:

Sub FormulaeToValues2()
With ActiveSheet
.UsedRange.Value = .UsedRange.Value
End With
End Sub

Regards

Trevor
 
P

Paul B

Trevor, be careful with this one, don't know why but it looks like it will
remove and super and sub scripts

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
D

Dave Peterson

It'll remove all of the character by character formatting in those cells--not
just the super/subscripts.
 
T

Trevor Shuttleworth

Paul

thanks for raising this ... worth being aware of. The alternative approach
of copy / paste special values would also have the same effect. I guess it
can be avoided by cycling through the cells with formulae but I expect this
would be a little slower than the original suggestion.

Sub FormulaeToValues3()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
cell.Value = cell.Value
Next
End Sub

I don't think you can apply formatting to cells with a formula in them so
this should be safe.

Regards

Trevor
 
P

Paul B

Trevor, "The alternative approach of copy / paste special values would also
have the same effect"
This does not happen on copy and paste values

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
T

Tom Ogilvy

No,

cells.copy
cells.pastespecial xlValues

doesn't have that problem.

At least not in xl97. What did you testing show and what version?
 
M

Mike

Trevor Shuttleworth said:
Paul

thanks for raising this ... worth being aware of. The alternative approach
of copy / paste special values would also have the same effect. I guess it
can be avoided by cycling through the cells with formulae but I expect this
would be a little slower than the original suggestion.

Sub FormulaeToValues3()
Dim cell As Range
For Each cell In
Click to expand...
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
cell.Value = cell.Value
Next
End Sub

I don't think you can apply formatting to cells with a formula in them so
this should be safe.

Regards

Trevor
Click to expand...


Trevor,

I didn't have any trouble with the Copy - Paste Special method either
(97 & 2000). I don't think you even need to use a loop in this
situation. Something like:

With ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
.Value=.Value
End With

should also work.


-Mike
 
D

Dave Peterson

If my formulas were non-contiguous, then that gave me bad results (xl2002).

But I could loop through each contiguous area in the range and it worked ok:

Option Explicit
Sub testme01()

Dim myArea As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
'no formula cells
Else
For Each myArea In myRng.Areas
With myArea
.Value = .Value
End With
Next myArea
End If
End Sub



Mike wrote:
 
P

Paul B

Tom, I used copy and paste values as you did with no problem, using excel
2000

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
T

Trevor Shuttleworth

Paul/Tom

sorry, poor testing on my part. I did not consider the wider ramifications
of simply using .value = .value in the first instance. Subsequent testing
of copy and pastespecial only showed what I expected to see ... which was
wrong.

Still, we live and learn. Thanks for enlightening me.

Regards

Trevor
 
M

Mike

Dave Peterson said:
If my formulas were non-contiguous, then that gave me bad results (xl2002).

But I could loop through each contiguous area in the range and it worked ok:
Click to expand...



Dave,

It looks like I should have been a little more creative & thorough
with my testing. When I tested the code that I posted earlier, I just
tested it on two areas that contained the same complex formula (=1+1).
Lesson learned.


Thanks for letting me know,

Mike
 
D

Dave Peterson

Ain't newsgroups a neat way to learn?

Dave,

It looks like I should have been a little more creative & thorough
with my testing. When I tested the code that I posted earlier, I just
tested it on two areas that contained the same complex formula (=1+1).
Lesson learned.

Thanks for letting me know,

Mike
Click to expand...
 
Top