Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,885
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,089
Default 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




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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






  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default 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






  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 413
Default 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


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,089
Default 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








  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default 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












  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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










  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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


  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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

  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default 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












  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,089
Default 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














  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default 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


  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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

  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 103
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer a name from one cell to another but leave clear if clear? Scoober Excel Worksheet Functions 3 May 22nd 09 02:55 AM
Formulae: Paste value formulae after doing an average operation Lim Excel Discussion (Misc queries) 4 April 20th 08 07:31 PM
clear the clear the web page email attachment lines MCrider Excel Discussion (Misc queries) 0 November 11th 07 11:05 PM
Clear all? sdmccabe Excel Discussion (Misc queries) 3 December 16th 05 08:51 PM
Searching TEXT in formulae, rather than results of formulae AndyE Excel Worksheet Functions 1 July 15th 05 10:57 AM


All times are GMT +1. The time now is 01:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"