VBA to Delete cell values only

[simora]

Hi:

Using VBA, I'm trying to delete only the values of a range of cells, leaving the formulas and formatting in place.


ActiveSheet.Range("R14:R18").SpecialCells(xlCellTypeConstants).ClearContent s

is not working.

Any help or ideas ?

Thanks

[lucas]

If your formula's operate using values from other cells that would be hard to do if those cells still have values.......better to clear those cells.....

in other words if you have a formula =A1+B1 in a cell you want to clear and you have values in A1 or B1.....that's where you need to clear the data.

[lucas]

Am I missing something here?

[simora]

I have formulas that reference other cells, but they will be set to zero by the time I get to this operation.

There's got to be a way to do this.

[Kenneth Hobs]

"Not working" could mean anything. Most likely, your problem is that there are no cells found that are constant so it errors with run-time error number 1004, no cells found. You have to account for errors so that it will "work".

e.g.
[vba]On Error Resume Next
ActiveSheet.Range("R14:R18").SpecialCells(xlCellTypeConstants).ClearContent s[/vba]

[simora]

Is there something basically wrong with the format of the code itself, oe is it just not going to work?
I've tried using (xlCellTypeAllFormatConditions).ClearContents as a substitute.
Still no go.

If I have a cell in that range that referencs cell A1 as in =A1 all I want is to clear the value so that =A1 remains as a formula, especially if A1 = " ".
Hope I'm making sense here.

Thanks

[Kenneth Hobs]

Your first method works fine. You do have to account for the case where no cells in your range R14:R18 has no values as I explained earlier. However, if the formulas in that range reference constant cells outside that range, the value of the formula will not change.

If you want to delete all the constant cell values for the activesheet then you need to use something like:
[VBA]On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents [/VBA]

Of course the formulas may result in a 0 being shown if you have the preferences set to show 0's or you used to =If's to adjust the formulas.

[simora]

Thanks for all your help, but that stuff never did work.
I just created a mirror sheet minus the values and after clearing the contents of the original sheet, I copied and pasted the exact range from the mirror sheet to re-establish the integrity of the sheet that was maligned by my VBA efforts.
A kludge, but it works.

[Kenneth Hobs]

That is confusing to me.

In the future, I recommend that you work on a copy of your workbook when you test other's code.

[Aussiebear]

Thanks for all your help, but that stuff never did work.

Why not?

Given that both Lucas & Kenneth have gone out of their way to assist you in this issue, I think you owe both of these gentlemen and more detailed response than the line above.

Perhaps it may have been more prudent to simply post your workbook so that they can see more of the issue than you have eluded to.

[simora]

Sorry about that:

The code s just the 1 line to clear the values of 4 cells while keeping the formulas, formatting, etc,,.

ActiveSheet.Range("R14:R18").SpecialCells(xlCellTypeConstants).ClearContent s

Whenever I run the code in Excel 2003, it clears everything, including the formulas

Didn't mean to be crude!
Thanks

[Kenneth Hobs]

Then you have something else going on as it works fine for me. Without seeing your xls, I don't know what that would be.

See my example to test.

[simora]

Hi:
Thanks for the post.
I took a look at your sheet. I now realize that somehow, I'm not getting across what I wanted to achive.
I want to clear the actual values on the sheet, but leave all of the formatting and formulas intact. Your sheet deletes cell contents that are not referenced by a formula, but it does not clear the actual cell values in the Range. I even tried this unsuccessfully
Range("R14:R18").SpecialCells(xlCellTypeAllFormatConditions).ClearContents

I was hoping that by posting my workaround that I had explained it all.
Sorry if I mislead you guys, and Thanks for your efforts.

[Kenneth Hobs]

I am more confused now.

If you have a formula you get a value. You need to design your formulas to account for the conditions you want.

If you want to delete formulas, that can be done. If you want to hide formulas, that can be done via custom numeric formats.

You can of course iterate a range and check each Value. If it is a formula, what did you want done? If it is a constant, what did you want done?

[Aussiebear]

Kenneth, I fear that Simora has either missed your point regarding posting a workbook, or simply doesn't want to.

Simora, Firstly by posting the workbook, it allows others to clarify any uncertainty that may have been raised in your posts, by looking first hand at your issue. Secondly, there may be others with a similar problem but are having trouble expressing the issue. These people will benefit from being able to see your workbook as well.

[simora]

I need to make a copy of that worksheet to post it. Never posted a worksheet here. Exactly where is the procedure for showing your worksheet?
Trying to keep it simple.

[GTO]

I need to make a copy of that worksheet to post it. Never posted a worksheet here. Exactly where is the procedure for showing your worksheet?
Trying to keep it simple.

Greetings Simora,

At the bottom of the thread, below the Quick Reply box, press the Go Advanced button. Once the page loads, below the msg window section is a Manage Attachments button... click it...

Hope this helps,

Mark

[Cyberdude]

I'm trying to figure out just what it is that he wants to do. It seems to be a display problem, i.e., if certain conditions occur, then make the contents of that cell disappear (look empty) but don't delete the formula that creates a displayed value. I don't think it's possible to to clear the displayed results of a formula unless:
1. You alter the formula to produce a blank value when those conditions occur, or
2. Use conditional formatting to make the font color the same as the background color so that the cell appears to be empty or blank, but the value will still be there.

If the value really must be eliminated because some other formula is dependent on what's in the cell, then solution 1 above will have to be used because there is no way to turn off the execution of a formula, but you can change what the formula produces.

Solution 2 above works just fine for many applications, but won't help if another formula is referencing that cell to get to get its value, since there will always be a value even if it appears to the user that it has been cleared.

[lucas]

Sid, that was the point I was trying to make in post 2 & 3.....

[elcarlson]

ActiveSheet.Range("R14:R18").ClearContents

This would be the correct code, "SpecialCells(xlCellTypeAllFormatConditions)" is not needed.