In Excel, can you hide a range of cells with a checkbox?

%3CLINGO-SUB%20id%3D%22lingo-sub-2384974%22%20slang%3D%22en-US%22%3EIn%20Excel%2C%20can%20you%20hide%20a%20range%20of%20cells%20with%20a%20checkbox%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20made%20a%20Excel%20worksheet%20for%20%22Paint%20to%20additive%22%20ratio%20for%20different%20materials%2C%20glass%2C%20metal%2C%20ect.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20want%20to%20use%20a%20checkbox%20to%20hide%20a%20range%20of%20cells%20with%20calculations%2Fresults%20for%20materials%20not%20used.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3ERange-%20E24%20to%20J28%20%3F%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2384974%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385095%22%20slang%3D%22en-US%22%3ERe%3A%20In%20Excel%2C%20can%20you%20hide%20a%20range%20of%20cells%20with%20a%20checkbox%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385095%22%20slang%3D%22en-US%22%3EYou%20can%20have%20that%20happen%20but%20it%20requires%20a%20macro%20(VBA).%3CBR%20%2F%3ELet%20me%20suggest%20an%20alternative%3A%3CBR%20%2F%3EA%20checkbox%20will%20'control'%20a%20cell%20and%20change%20the%20value%20of%20that%20cell%20accordingly.%20Then%20use%20Conditional%20Formatting%20using%20a%20user%20defined%20formula%20(%3D%24M%241%20if%20M1%20is%20the%20cell%20the%20checkbox%20is%20linked%20to)%20to%20'hide'%20the%20range%20(i.e.%20format%20those%20cells%20with%20the%20same%20text%20color%20and%20background)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

I made a Excel worksheet for "Paint to additive" ratio for different materials, glass, metal, ect.
I want to use a checkbox to hide a range of cells with calculations/results for materials not used.
Range- E24 to J28 ??

3 Replies
You can have that happen but it requires a macro (VBA).
Let me suggest an alternative:
A checkbox will 'control' a cell and change the value of that cell accordingly. Then use Conditional Formatting using a user defined formula (=$M$1 if M1 is the cell the checkbox is linked to) to 'hide' the range (i.e. format those cells with the same text color and background)
I was leaning towards Macro. I have 3 different ranges of cells w/ some complex formulas. I know somewhat about hiding columns and rows not a range. ex. E24 to J28
yeah, there is no way to 'hide' a range that doesn't include full row or full columns (not even sure what that would look like except in the sense or them being a white block, which is what conditional formatting would produce).
So a macro could do the essentially same thing making those range(s) white out, but conditional formatting would be much easier. I'm not sure what your complex formulas have to do with it but if you don't want to use conditional formatting, and those ranges are formulas or constants, you could also simply add an if statement to check for the checkbox state and either display the value or ""