Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

macro clear value error

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Macro to clear #VALUE! error cells

Macro to clear #VALUE! error cells

ResolvedVersion 2003

Calum has attended:
Excel Intermediate course
Excel Advanced course

Macro to clear #VALUE! error cells

Please advise on how I can write a macro to clear ALL cells in a spreadsheet that contain the #VALUE! error messages that are created in cells that lack a reference number as part of their formula once I have dragged formulas down a column.
Thanks,
Calum

RE: Macro to clear #VALUE! error cells

Hello Calum,
I hope you enjoyed your Excel training sessions with us.

In response to your question, I would suggest that rather than use a macro to correct your errors, that instead you use a formula from the start that will hide the errors. We can do this with the IF and ISERROR functions.

For example, let's assume your formula is a basic multiplication:
=A1*B1

To correct errors for this formula, your new formula would be:
=IF(ISERROR(A1*B1),"",A1*B1)

The empty quotes will return a blank cell. However, you can add any messaging or number in this cell. Text must be entered within the quotation marks, but numbers can just be on their own.

I hope this resolves your question. If it has, please mark this question as resolved. If you require further assistance, please reply to this post. And please feel free to ask any other questions you may have.

Have a great day.

Regards,
Mara
Microsoft Office Specialist Master Trainer

RE: Macro to clear #VALUE! error cells

Hi Mara,
Thanks for yours.
The formula I frequently use is finding the geometric difference between two percentages, for instance:
=((1+A10/100)/(1+$A$5/100)-1)*100

Am I right in interpreting your suggestion - that I should use:
=(IF(ISERROR(((1+A10/100)/(1+$A$5/100)-1)*100),"",((1+A10/100)/(1+$A$5/100)-1)*100) ?

If so, it's pretty unwieldy...even if it works!
I was really looking for a method of clearing all the error messages in a spreadsheet once I have completed dragging down the formulas...often 1000 rows and 100 columns per table?
Thanks,
Calum

RE: Macro to clear #VALUE! error cells

Hi Calum,
Unfortunately I'm not aware of a way of clearing the errors otherwise, since find and replace does not work for errors. Although the formula you have written looks unwieldy, it is written correctly, and woud clear the errors up front.

Sorry I cannot be of more help.

Regards,
Mara

Excel tip:

Sorting List Subtotals

If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.