Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete Hide Display

Posted on 2014-01-24
8
Medium Priority
?
273 Views
Last Modified: 2014-01-24
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
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39805924
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39805938
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39805948
May i have sample book pls....
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 39805983
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
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39806003
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
 
LVL 31

Expert Comment

by:gowflow
ID: 39806005
are you referring to my solution ???
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 39806011
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
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39806024
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question