Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Delete Hide Display

Hi Expert,

Is there any way to delete all error messages from whole sheet?  Hide & Display as per required.

i had attached file in which there is few error messages i want to delete that by clicking Delete Button.
i have two more buttons Hide & Display - if there is the  case that if i want that formula remain in the sheet - so hide out buy changing text color of error messages to its background (Cell Specific background color) color &  on click of display button all error message revert to its original or black color.



Thanks
Error-Remove.xlsm
0
Naresh Patel
Asked:
Naresh Patel
  • 3
  • 3
  • 2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
Created by using the Macro recorder, this line will select all formulas that result in an error:

Selection.SpecialCells(xlCellTypeFormulas, 16).Select

Open in new window

Thanks
Rob H
0
 
Rob HensonIT & Database AssistantCommented:
Alternatively, if there are specific cells that you know could give an error result, incorporate an IF statement into the formula using a separate input cell to decide whether an error should be shown or not, eg A1 could be Hide or Show, the value of which could be changed by the button then a formula such as the erroneous division in would change to:

=IF(A1="Hide",IFERROR(C6/D6,""),C6/D6)

Thanks
Rob H
0
 
Naresh PatelTraderAuthor Commented:
May i have sample book pls....
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:
Is this what you want ? I created a copy of sheet1 data in sheet1 (2) so if you delete you can get them back.

Note that #N/A N/A is not an error it is considered text !!

check this file and let me know
gowflow
Error-Remove.xlsm
0
 
Naresh PatelTraderAuthor Commented:
yes this is what i want. only last thing do i able to use this function or code to all file in my pc for all user by creating button in quick access tool bar? or any kind of add in tab?
0
 
gowflowCommented:
are you referring to my solution ???
gowflow
0
 
gowflowCommented:
If you are pointing to my solution then there is a tweak we can create to have this available to all workbooks which will be by creating a menu to access all this. But then it has to be designed differently.

If you want and you like this simply accept this one and post a new one with link too this question and I will be glad to do it for you as will need to get all this in module then built menu to access each one on its own ....

gowflow
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now