Clear formulae
 

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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

Hi
try
activecell.value=activecell.value

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


--
Regards
Frank Kabel
Frankfurt, Germany


Stuart wrote:
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004




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

Clear formulae
 

Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

cells.copy

cells.pastespecial xlValues

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

Many thanks, all.

Regards.

"Frank Kabel" wrote in message
...
Hi
try
activecell.value=activecell.value

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


--
Regards
Frank Kabel
Frankfurt, Germany


Stuart wrote:
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

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



Paul B wrote:

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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004





--

Dave Peterson

Clear formulae
 

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(xlCellTypeFormu las, 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


"Paul B" wrote in message
...
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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

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 **
"Trevor Shuttleworth" wrote in message
...
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(xlCellTypeFormu las,
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


"Paul B" wrote in message
...
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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

No,

cells.copy
cells.pastespecial xlValues

doesn't have that problem.

At least not in xl97. What did you testing show and what version?
--
regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote in message
...
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(xlCellTypeFormu las,
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


"Paul B" wrote in message
...
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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

"Trevor Shuttleworth" wrote in message
...
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(xlCellTypeFormu las, 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



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(xlCellTypeFormu las)
.Value=.Value
End With

should also work.


-Mike

Clear formulae
 

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(xlCellTypeFormu las)
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:
<<snipped

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(xlCellTypeFormu las)
.Value=.Value
End With

should also work.

-Mike

--

Dave Peterson

Clear formulae
 

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 **
"Tom Ogilvy" wrote in message
...
No,

cells.copy
cells.pastespecial xlValues

doesn't have that problem.

At least not in xl97. What did you testing show and what version?
--
regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote in message
...
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(xlCellTypeFormu las,
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


"Paul B" wrote in message
...
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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

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


"Tom Ogilvy" wrote in message
...
No,

cells.copy
cells.pastespecial xlValues

doesn't have that problem.

At least not in xl97. What did you testing show and what version?
--
regards,
Tom Ogilvy

"Trevor Shuttleworth" wrote in message
...
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(xlCellTypeFormu las,
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


"Paul B" wrote in message
...
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 **
"Trevor Shuttleworth" wrote in message
...
Stuart

one way:

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

Regards

Trevor


"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004

Clear formulae
 

"Dave Peterson" wrote in message
...
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:




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

Clear formulae
 

Ain't newsgroups a neat way to learn?


Mike wrote:

"Dave Peterson" wrote in message
...
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:


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

--

Dave Peterson

Clear formulae
 

With Activesheet.UsedRange
.Value = .Value
End With

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Stuart" wrote in message
...
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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.674 / Virus Database: 436 - Release Date: 02/05/2004