Hide rows and columns automatically based on value

[Faaiza]

Hi

I have to develop an excel sheet (sheet name "Graph Data") where a row or column is hidden automatically if the value in Column B or Row 2 is "Not Applicable" and unhide the row and column automatically when the cell value changes. I have uploaded the excel workbook

An example:

Buy: Sc1Not applicableBuy: Sc3Not applicableRent: Sc2Rent: Sc3
Direct111111
Indirect111111
Not applicable111111
Capex111111
Not applicable111111
total111111
NpV111111
etc.

The values in the row 2 and column B change regularly automatically based on other information in other sheets. And then the row should hide or unhide automatically. This hiding and unhiding of rows and columns must only be automatically applied to the sheet "Graph Data" and not to all the other sheets in the workbook

Can you please help me?

Thanks a lot

Faaiza



PS I've read through answers related to my question, but they unfortunately didn't help me as the related questions were similar, but not the same (and I didn't get how the macros are built and thus wasn't able to adapt them)

 

[p45cal]

In the attached on Sheet1 are some cells shaded green. These are linked to the same cells in Graph Data sheet. This is some way towards satisfying "The values in the row 2 and column B change regularly automatically based on other information in other sheets".
Change any of these green cells to test.
The macro is very basic at the moment but can be tweaked.

 

[Faaiza]

Thanks so much for the file it works perfectly on the example that you attached.

I tried copying the code into the workbook that I am using and adjusted the row 2 and column 2 to row 12 and column 8 but had a problem. The macro seems to run endlessly and I have force Excel to close.

To verify that I am changing the coding correctly and I applied the row and column changes to the workbook you sent and it works perfectly there. My workbook also has data tables, could this be causing the problem?

Thanks
Faaiza

 

[p45cal]

My workbook also has data tables, could this be causing the problem?

I don't know.
If you want to send me the file privately, ask me for an email address and I'll send you it via a private conversation here at Chandoo.

 

[Faaiza]

Thanks so much that would be very helpful. May I please have your email

 

[p45cal]

I've started a 'conversation' with you…