Clear Values not formulas. Can u ClearValues instead of .ClearContents 2

[coolcarls]

I need to clear just the values not the formula.
This is working for me Worksheets("Sheet1&quot.Range("A1&quot.ClearContents
But it removes the formula too. Any easy war around this???????????
Thanks
Carl

 

[Dreamboat]

You cannot have a formula without a returned value!

If you want the cell to be blank, then you must do it in the formula...maybe like this:

=if(isblank(a1),"",a1*b1)

That will return a blank cell (that still has the formula in it) if a1 is blank---like until you have data entered. There are others. What's your formulas? Anne Troy

http://www.TheOfficeExperts.com

 

[coolcarls]

I am entering infor into column A. When I have entered it all in I use a macro to copy the data and then tranferr it to another sheet then clear the column so it's ready for the next set of data entry. So I don't think that the formula you suppliedwould work in this case. It is quite a nifty formula though and I'm sure I'll be useing it somewhere soon.
Thanks a lot

 

[Loomah]

Hi
If you want to clear the constants (ie not the formulas) from a sheet you could use one of these. The first is indiscriminate and will clear all values, the second will clear the values of the first column only. Note this will only be column A if column A has been used.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstants).ClearContents

However, if you are moving data, would it not be an idea to cut the data rather than copy it. This may make your formulas react in a seemingly bizaar way but if you don't have that problem you will find column A empty without clearing the contents!

I think that's what you wanted?????

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[coolcarls]

Loomah,
They both are great and they both work, thanks a lot!

 

[coolcarls]

Hey Loomah,
I have a dumb question....I don't want to clear the whole sheet just certain cells, can I change this formula ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents" to do that?

 

[coolcarls]

Duh! I got it.
Private Sub CommandButton1_Click()
ActiveSheet.Range("A115&quot.SpecialCells(xlCellTypeConstants).ClearContents
End Sub

Thanks