Improving a macro, so that it either hides, or unhides all the columns

**davidx** · 11-03-2015, 03:12 PM

I have the below macro:

Please Login or Register  to view this content.

If I have columns b:e unhidden, but columns g:j hidden. And run this macro. It will hide B:E, and unhide g:j

What I want to do, is make it so that it will hide all of the columns: b:e g:j l:o q:t
and then unhide all of them if I press it again. And if I press it again it will hide them all. So that whatever the prior situation, after pressing the button, all the columns will be either hidden or unhidden simultaneously.

Does anyone have any idea how to achieve this? thanks very much

**TMS** · 11-03-2015, 03:25 PM

Please Login or Register  to view this content.

Regards, TMS

**davidx** · 11-09-2015, 06:29 AM

Hi TMS,

Thanks for this, but I would like it to also unhide all the columns together when i run the macro for a second time? So I can alternate the columns between hidden and uhidden (Thanks, and sorry for crazily late reply)

**TMS** · 11-09-2015, 06:57 AM

Have you tried it? That's exactly what it does. Note the "bHide = Not bHide"

Or, a bit shorter but exactly the same functionality:

Please Login or Register  to view this content.

Regards, TMS

**davidx** · 11-10-2015, 04:49 AM

Hi TMS. Thanks for your reply, but it really isnt working for me. I am in Excel 2010. I have saved the macro in the attached. And running it once hides all the columns. But running again doesn't seem to unhide them. Does it work for you!? thanks so much TMSBook3.xlsx

**JOHN H. DAVIS** · 11-10-2015, 09:44 AM

Your original code works for me?

Please Login or Register  to view this content.

**davidx** · 11-10-2015, 03:19 PM

Hi John. the original code doesn't fulfill my needs, because lets say b:e are hidden, but g:j are unhidden. I want to press a button that will hide both b:e and g:j. And then unhide both when I press it again. Unfortunately my code would unhide b:e but hide g:j. Does that clarify? thanks

**TMS** · 11-10-2015, 03:52 PM

@davidx: I'm guessing you just copied the code and not the public variable? If that's the case, it wouldn't work.

@John: yes, the code will work as expected, provided the state of the columns isn't changed manually. If that happens, you may get a situation where the states swap but some columns will be visible when they should be hidden.

With the public variable, everything works off the last state, True or False.

Have a look at the sample workbook attached.

Regards, TMS

**JOHN H. DAVIS** · 11-10-2015, 03:58 PM

@ TMS - I'm just realizing that. To be honest I was stomped as well. Thanks for the insite.

**TMS** · 11-10-2015, 04:03 PM

You're welcome.

**davidx** · 11-10-2015, 05:01 PM

Hi TMS. 1. you are a genius. 2. Is there no solution to this then?

For example, lets say I have labelled columns in the attached as quarters full years. Now for whatever reason, I have manually hidden column F. Unfortunately no combination of those macros will give me all (and only) the Full years; or all (and only) the quarters. Is this just unsolvable? thanks so much Hide - Unhide Columns Davidx.xlsm

**TMS** · 11-10-2015, 05:33 PM

Ahhhh ... the old moving goalpost

Here's a thought ...

Please Login or Register  to view this content.

See the attached updated workbook.

Regards, TMS

**TMS** · 11-10-2015, 05:34 PM

Oh, sorry, keep pressing the new button ...

**davidx** · 11-11-2015, 04:13 AM

Wow TMS, that is really clever stuff, could never have done it myself thanks. If you have a bit of time, is there anyway to improve the macro further so that of the three states it rotates through, it never hides all the columns. So instead of having 3 states. It has only two. i.e. FYs and quarters? If you can't be bothered that's fine too. Thanks very much already!!!!!

**TMS** · 11-11-2015, 04:31 AM

You're welcome. Thanks for the rep.

Actually, it's 4 states down to 3

... None, FY, Quarters, All. But, having established the principle, it's just a case of tweaking the cycle. In the code below, when you first open the workbook, iState will be zero (0). So, on the first press of the button, it will be incremented to 1, which will show just the Quarters; next press, it will increment to 2 and show Full Years; next press, it will increment to 3 and reset to 0 to show all columns.

Please Login or Register  to view this content.

**davidx** · 11-11-2015, 04:47 AM

You are an absolute hero! Thank you so much, I have tried for about two years to solve this! thank you; thank you; thank you!!!!!

**TMS** · 11-11-2015, 05:00 AM

You are very kind.

Again, you're welcome

Improving a macro, so that it either hides, or unhides all the columns

LinkBack

Thread Tools

Rate This Thread

Display

Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Re: Improving a macro, so that it either hides, or unhides all the columns

Thread Information

Users Browsing this Thread

Similar Threads

Need Help writing a VBA for a worksheet calculate event that hides/unhides rows

Button appeared at top that hides/unhides cells and spreadsheet

Rationalizing my VBA code (it hides/unhides rows and then autofits them)

[SOLVED] Macro code that hides or unhides rows in other worksheets

Macro that hides/unhides sheets depending on cell value

Userform that hides/unhides worksheets

Button hides unhides columns, how?

Bookmarks

Bookmarks

Posting Permissions