SOLVED

Invisable Rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1382380%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382380%22%20slang%3D%22en-US%22%3EI%20believe%20you%20will%20need%20VBA%20code%20for%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382393%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382393%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20response%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B!%3CBR%20%2F%3EI'm%20wondering%20which%20VBA-Code%20I%20need.%20I%20know%20how%20I%20find%20the%20VBA-code%20but%20I%20would%20like%20to%20know%20how%20I%20should%20write%20this%20code.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382417%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F664595%22%20target%3D%22_blank%22%3E%40StijnCl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVBA%20if%20you'd%20like%20to%20hide%20entire%20rows.%3C%2FP%3E%0A%3CP%3EIf%20to%20hide%20cells%20values%20in%20these%20rows%20you%20may%20apply%20conditional%20formatting%20rule%20applying%20white%20on%20white%20color%20for%20them%20(or%20like).%20However%2C%20they%20will%20be%20visible%20in%20formula%20bar%20if%20user%20focuses%20on%20such%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382426%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F664595%22%20target%3D%22_blank%22%3E%40StijnCl%3C%2FA%3E%26nbsp%3Byou%20will%20need%20a%20macro%20that%20runs%20every%20time%20a%20cell%20value%20changes%20(event-driven%20macro)%3C%2FP%3E%3CP%3Eyou%20can%20google%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20if%20you%20want%20me%20to%20build%20the%20code%2C%3C%2FP%3E%3CP%3Eis%20it%20possible%20for%20you%20to%20share%20the%20file%3F%3CBR%20%2F%3EYou%20may%20remove%20confidential%20info%20from%20the%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382458%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3B%20I%20tried%20to%20implement%20the%20results%20I%20found%20on%20Google%2C%20but%20I%20it%20didn't%20work...%3CBR%20%2F%3EI%20attached%20the%20file%20in%20this%20comment.%20It's%20about%20the%20last%20two%20sheets.%20The%20cell%20that%20contains%20the%20question%20with%20the%20Yes%20%2F%20No%20answer%20is%20dark%20blue.%20I%20made%20the%20rows%20that%20need%20to%20vanish%20light%20blue.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382470%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382470%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20your%20for%20your%20answer%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B!%3CBR%20%2F%3EUnfortunately%20I%20can't%20write%20a%20VBA-code%20that's%20why%20I%20have%20this%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382364%22%20slang%3D%22en-US%22%3EInvisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EQuestion%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHow%20do%20I%20create%20(in)visible%20cells%20based%20on%20a%20value%20in%20a%20certain%20cell%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3ESpecifications%3CBR%20%2F%3E%3C%2FSTRONG%3EIn%20an%20Excel%20file%20I%20created%20I%20have%20a%20number%20of%20rows%20that%20only%20need%20to%20be%20visible%20when%20a%20certain%20question%20is%20%22yes%22.%26nbsp%3BThe%20closed%20question%20%22Yes%22%20%2F%20%22No%22%20(In%20Dutch%20%22Ja%22%20%2F%20%22Nee%22)%20is%20in%20cell%20B35.%20For%20this%20I%20made%20a%20Dropdown-menu%20where%20the%20choice%20is%20made%20possible.%20The%20rows%20that%20need%20to%20be%20visible%20when%20the%20answer%20in%20cell%20B35%20is%20%22Ja%22%20in%20the%20Excel%20file%20are%2036%3A44.%20What's%20the%20easiest%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1382364%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382643%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F664595%22%20target%3D%22_blank%22%3E%40StijnCl%3C%2FA%3E%26nbsp%3BTry%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20Macros%20are%20for%20fixed%20rows%20and%20cells.%20If%20you%20insert%2Fdelete%20rows%2C%20it%20might%20fail.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382665%22%20slang%3D%22en-US%22%3ERe%3A%20Invisable%20Rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F662966%22%20target%3D%22_blank%22%3E%40bhushan_z%3C%2FA%3E%26nbsp%3BYou're%20the%20best!%20It%20was%20just%20the%20other%20way%20around.%20So%20%22Ja%22%20was%20visable%20and%20%22Nee%22%20was%20invisible.%20But%20I%20changed%20it%20myself.%20Thanks%20for%20your%20help!%20Much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Question

How do I create (in)visible cells based on a value in a certain cell?

Specifications
In an Excel file I created I have a number of rows that only need to be visible when a certain question is "yes". The closed question "Yes" / "No" (In Dutch "Ja" / "Nee") is in cell B35. For this I made a Dropdown-menu where the choice is made possible. The rows that need to be visible when the answer in cell B35 is "Ja" in the Excel file are 36:44. What's the easiest way to do this?

8 Replies
Highlighted
I believe you will need VBA code for this.
Highlighted

Thank you for your response @bhushan_z !
I'm wondering which VBA-Code I need. I know how I find the VBA-code but I would like to know how I should write this code.

Highlighted

@StijnCl 

VBA if you'd like to hide entire rows.

If to hide cells values in these rows you may apply conditional formatting rule applying white on white color for them (or like). However, they will be visible in formula bar if user focuses on such cell.

Highlighted

@StijnCl you will need a macro that runs every time a cell value changes (event-driven macro)

you can google it

 

Also, if you want me to build the code,

is it possible for you to share the file?
You may remove confidential info from the file.

Highlighted

@bhushan_z  I tried to implement the results I found on Google, but I it didn't work...
I attached the file in this comment. It's about the last two sheets. The cell that contains the question with the Yes / No answer is dark blue. I made the rows that need to vanish light blue.

Highlighted

Thank your for your answer @Sergei Baklan !
Unfortunately I can't write a VBA-code that's why I have this question.

Highlighted
Best Response confirmed by StijnCl (Occasional Contributor)
Solution

@StijnCl Try the attached file.

 

Note: Macros are for fixed rows and cells. If you insert/delete rows, it might fail.

 

@bhushan_z You're the best! It was just the other way around. So "Ja" was visable and "Nee" was invisible. But I changed it myself. Thanks for your help! Much appreciated!