PDA

View Full Version : VBA to Delete cell values only



simora
12-20-2008, 01:57 PM
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).ClearContents

is not working.

Any help or ideas ?

Thanks

lucas
12-20-2008, 02:10 PM
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
12-20-2008, 02:38 PM
Am I missing something here?

simora
12-20-2008, 02:55 PM
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
12-20-2008, 02:58 PM
"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.
On Error Resume Next
ActiveSheet.Range("R14:R18").SpecialCells(xlCellTypeConstants).ClearContents

simora
12-20-2008, 03:06 PM
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
12-20-2008, 06:47 PM
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:
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

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
12-21-2008, 11:18 AM
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
12-21-2008, 12:14 PM
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
12-26-2008, 03:51 AM
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
12-27-2008, 06:35 AM
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).ClearContents

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

Didn't mean to be crude!
Thanks

Kenneth Hobs
12-27-2008, 09:16 AM
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
12-27-2008, 03:00 PM
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
12-27-2008, 03:26 PM
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
12-27-2008, 03:47 PM
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
12-27-2008, 11:56 PM
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
12-28-2008, 06:54 AM
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
12-29-2008, 03:32 PM
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
12-29-2008, 03:46 PM
Sid, that was the point I was trying to make in post 2 & 3.....

elcarlson
07-30-2009, 09:59 AM
ActiveSheet.Range("R14:R18").ClearContents

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

elcarlson
07-30-2009, 09:59 AM
ActiveSheet.Range("R14:R18").ClearContents

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