Making cell value blank

**phil2006** · 09-20-2008, 10:05 AM

Hi,

I have a number of cells with formulae. The following is a simplified version:

Please Login or Register  to view this content.

I am using this because if the sum is less than 5 I do not want anything to be in the cell. This is because the sheet then copies over into a different workbook. The second workbook needs the value to be blank to run properly. Using "" does not work unfortunately. Is there anything I can replace that with to save me having to delete all values less than 5 within the range manually?

Thanks in advance,

Phil

**davesexcel** · 09-20-2008, 10:53 AM

Using "" does not work unfortunately.

Why doesn't it work?
Maybe when you copy and paste, use paste special, values only

**phil2006** · 09-20-2008, 11:19 AM

Thanks Dave but I have already tried pasting values only. I have no idea why that doens't work. The second sheet is not one that I have desiged but it will not accept "" as no value. If I clear the cell contents manually it works for some reason.

**martindwilson** · 09-20-2008, 03:46 PM

the second sheet must be using =if(isblank)...... as a test
your function although not showing anything is not blank
that is only achieved when using delete
test with
your function in A1 =ISBLANK(A1) in A2 returns FALSE =ISTEXT(A1) returns true
even copying paste/values transfers that info. looks like the second sheet needs reworking using ISTEXT as a test

**phil2006** · 09-21-2008, 05:39 PM

Thanks Martin. Unfortunately I cannot edit the second sheet. It doens't look like I am going to find any sort of conditional formatting that can clear cells completely.

Thanks again,

Phil

**Mark@Work** · 09-23-2008, 11:18 AM

The only way I can think of is to write a macro that will check each cell, and "properly" delete everything in all the "" cells.

**Mark@Work** · 09-23-2008, 02:12 PM

Try using this macro:

Please Login or Register  to view this content.

It will delete the contents of any cell where the value is "".
Note that this will clear out any formulas etc where the result is "".

Many thanks again to Richard (from the excel programming forum) for his assistance on this.

Mark.