Hi Folks,
I'm working on a massive Excel spreadsheet- perhaps as large as 25 million cells.
About 1/3 of the cells contain the error: #VALUE!
In the hopes of speeding things up, I would like to convert these to blank cells. I.e. remove the error message and underlying formula.
Is it possible to make a simple formula that I can just drag across the affected cells that will identify each "#VALUE!" cell and then replace it with blank cell?
Last edited by lamkoid; 09-18-2009 at 01:30 PM.
You could change your formulas to use the iferror wrapper... making the condition for a failure = ""
For example
=IFERROR(A1/B1,"")
If A1 = 1 and B1 = 0, instead of getting an error, no value is displayed. That way you don't lose the formula. So, if later there should be a value displayed you won't have to rewrite anything.
Do you have other error values that should persist ?
If not and you truly want to override error values then something along the lines of:
(the above can be done manually also by highlighting affected range on given sheet pressing F5 -> Special -> Formulas -> Errors (uncheck other options) -> OK -> hit Delete)
Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Hey DonkeyOte,
That F5 trick was awesome. It allowed me to overlap the highlight onto cells with data and formuals I needed to be left unaffected, and it removed the error values and the underlying formulas, as advertised.
It probably would have taken me about a year to figure that out on my own.
Using this forum is so rewarding, I feel like I am doing something illegal, or immoral.
Wow... that's pretty cool. I already did it to clean up one of my bigger sheets too. Learn something every time I come to this site... THANKS!
WRT the VBA solution on large spreadsheets, suggest you read http://www.rondebruin.nl/specialcells.htm before trying.
Entia non sunt multiplicanda sine necessitate
thanks shg - I was not aware of that issue previously.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks