Remove multiple #VALUE!

**CameronP** · 07-27-2012, 02:42 AM

Hi,

I have a spreadsheet where I have a formula entered into a cell and dragged down over 4900+ cells. The formula is =LEFT(E2,LEN(E2)-4) effectively it removes the last 4 characters from the cells in the row next to it.

Now not all of the cells used in the calculation contain data, some are empty. When this formula tries to do the calculation on an empty it returns "#VALUE!" in the output cell. Normally I can just click on the cell and delete the formula for that specific one but when I am dealing with 4900+ cells and at least 20% contain an error this is a VERY time consuming process. I have included an example with only 5 or 6 fields filled out, but image it on a much larger scale.

example.xls

Cheers,

**tom1977** · 07-27-2012, 02:52 AM

Hi try this

Please Login or Register  to view this content.

**CameronP** · 07-27-2012, 03:00 AM

Hi tom,

Thanks for the reply, the only problem is that I am very new to excel programming and VBA. I am not really sure how to apply a fix like this. My knowledge of excel programming goes as far as being able to write a formula in a cell and apply it. I am very much a 'newb' at VBA and macros so if you could just help me out as to how I apply this code that would be appreciated.

**abousetta** · 07-27-2012, 03:06 AM

Instead of clearing the errors, why not just add an if statement to check if the cell is blank first:

Formula:

Please Login or Register  to view this content.

You will notice I add -- before the Left function to convert the result to a number (as in your example). If it's not going to be a number then remove the double negative signs.

**CheshireCat** · 07-27-2012, 03:07 AM

Try this formula:

Please Login or Register  to view this content.

**tom1977** · 07-27-2012, 03:09 AM

Ok so You can make the same manually
1.select your range
2. press F5
3. choose "special"
4.choose "formulas" and "Errors"
5. press ok
6. press delete
and voila!!

**CameronP** · 07-27-2012, 03:24 AM

Thank you to all that replied, in particular tom and abousetta.

Stars to all of you. In the end I went with abousetta's solution as it seems logical to me. I am from a primarily Java and Java "Style" Syntax background and I am kicking myself for not thinking of that in the first place. Got to love a basic IF statement, I am just not used to them in excel or VBA yet.

Problem is solved, thread closed.

Thanks.