Log in

View Full Version : [SOLVED:] Remove formulas


ermis1975
10-15-2005, 12:43 AM
Hello...

How can I remove a formula from a cell (without protect a worksheet)?

Thanks
mdmackillop
10-15-2005, 03:30 AM
The following will remove the formula and return the values in a selection (one or more cells). The first will work with a contiguous range, the second with non-contiguous cells.



Sub CaPSCon()
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

Sub CaPSNonCon()
Dim cel
For Each cel In Selection
cel.Copy
cel.PasteSpecial Paste:=xlPasteValues
Next
Application.CutCopyMode = False
End Sub
Bob Phillips
10-15-2005, 03:51 AM
Sub CaPSNonCon()
Dim cel
For Each cel In Selection
cel.Copy
cel.PasteSpecial Paste:=xlPasteValues
Next
Application.CutCopyMode = False
End Sub




Sub CaPSNonCon()
Dim cel As Range
For Each cel In Selection
cel.Value = cel.Value
Next
End Sub

What does '...(without protect a worksheet)...' mean here?
xCav8r
10-15-2005, 06:30 AM
Perhaps without unprotecting a worksheet?
mdmackillop
10-15-2005, 06:39 AM
It might relate to using the hidden property of the cell.
Bob Phillips
10-15-2005, 06:40 AM
Mights and maybes. One person knows.
Cyberdude
10-15-2005, 05:04 PM
xld, there's something very elegant (simple & short) about your solution. I think the "cel.value = cel.value" is a preferred technique for removing formulas. I use it a lot.
mdmackillop
10-15-2005, 05:20 PM
xld, there's something very elegant (simple & short) about your solution.
Agreed.:thumb
ermis1975
10-16-2005, 05:13 AM
Thnaks all..(It means that, to hide a formula now I use the hidden property of a cell and after I protect the sheet to remove the formula)
brettdj
10-16-2005, 07:52 AM
And you can also wipe out the formulas in a specific formula range hit using the much under-utilised SpecialCells methods.



'in case there are no formulas
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas).Formula = Cells.SpecialCells(xlCellTypeFormulas).Value
XL-Dennis
10-16-2005, 08:37 AM
..or as Juan Pablo shows here:
http://www.dicks-blog.com/archives/2005/10/14/mouse-shortcuts/ :)
xCav8r
10-16-2005, 08:41 AM
or as Juan Ski shows here:
http://vbaexpress.com/kb/getarticle.php?kb_id=377 ;)
Zack Barresse
10-18-2005, 10:17 AM
Beware, most of these code solutions will fail if A1 is a merged cell. Juan Pablo's solution Dennis provided the link for should not fail. Beware the merged cells when coding..
Bob Phillips
10-18-2005, 10:41 AM
Beware, most of these code solutions will fail if A1 is a merged cell. Juan Pablo's solution Dennis provided the link for should not fail. Beware the merged cells when coding..

Anyone who uses merged cells deserves whatever befalls them http://vbaexpress.com/forum/images/smilies/devil.gif. They are evil in a spreadsheet's clothing.
Cyberdude
10-18-2005, 12:06 PM
Zack, I tried doing a ".Value = .Value" to remove a formulas in a merged cell and it worked just fine. Are there any special conditions associated with a merged cell that would cause a problem?
Zack Barresse
10-18-2005, 12:40 PM
The merged cell must be A1.
Cyberdude
10-18-2005, 02:47 PM
OK, thanx.
brettdj
10-18-2005, 04:29 PM
Anyone who uses merged cells deserves whatever befalls them http://vbaexpress.com/forum/images/smilies/devil.gif. They are evil in a spreadsheet's clothing.
So true

When I started with VBA I was staggered to see how often code fell over because of merged cells. I now loathe them
Cyberdude
10-19-2005, 01:47 PM
Interesting ... actually I use quite a few of them, and I can't say that I've had any problems. Perhaps I've just not tried to reference them in code. Well, yes I do. I just checked, and it seems that they are somewhat position dependent. Some cells might give problems, while other do not. Not sure why. Curious.