Toggle hide columns

[spacecaptainsuperguy]

This has been addressed by a number of other posts but not exactly the way I'm looking to do it, so I'm hopeful y'all can help. I have a large data table that spans columns A:AM that a number of people use. For person #1 they may only want to see a handful of columns and will hide the ones that don't pertain to them and for person #2 it would be a different set of columns and for person #3 yet another set of columns. What I'd like to do is create a macro button for each of them that hides only the columns they want hidden from view. (That part is easy enough) Problem is that it is typical for someone to hide columns, make their changes and then save and close with columns still hidden. Whoever gets into it next has to manually unhide all the columns and hide the ones they want hidden. I use all of the fields so I'm constantly unhiding columns. I was hoping to make a code for everyone that toggled between hiding their particular columns and then unhiding all hidden columns. I would customize the macro for each person to hide their particular columns but it would universally unhide all. I just can figure out how to get it to toggle between those two. To put it in simple terms, button press 1: unhide all columns and just hide the ones I want hidden, button press 2: unhide all columns.

Any idea are much appreciated.

 

[spacecaptainsuperguy]

Micron, to your point, and after reading my original post, I was not as clear as I should have been. When I said "make a code for everyone", my intent was to make a specific code that was individual to each person's needs. Since it was a small code, I assumed that might be the easiest route and my explanation was not as clear as it should have been. The above is what I was able to come up with on my own which appears to have solved my problem. If there is a better idea out there, I'm all ears and eager to learn. As Lilgto64 suggested, these are all saved to the individual's personal macro workbook so that macros can be used on any Excel file along with a handful of others I have made for my fellow employees.

As to your point: "Also, if there were hidden columns (because someone saved wb with hidden columns), then your hiding code lines won't even execute because Counter will be greater than zero", that is exactly the point of the macro to the best of my ability. If anything is hidden, unhide it. If nothing is hidden, hide the columns I want hidden. Toggle on, toggle off. If you can present a better way for me to make this happen, that is what I'm here for.

As to your point about 16 thousand columns. Thankfully the macro executes in the blink of an eye and I designed it to the best of my ability. If there is a way to tighten that up, that is what I'm here for.

Lilgto64, thanks for that code. I'll dig into your suggestions.

 

[Micron]

What I showed you was specific to everyone's means as long as you had the helper columns. It's the approach I would take as I'd rather let someone edit their choices on a sheet rather than have to delve into each person's code to make alterations for them.

If anything is hidden, unhide it. If nothing is hidden, hide the columns I want hidden.

I must have misunderstood the requirement then. I interpreted the requirement as "if anything is hidden, unhide it, then hide the columns I want hidden". According to how I interpret your code, that won't happen but now it seems that is not a requirement.

As for looping over 16K+ columns, carry on with that if you're happy with what you have. However, it is not too difficult to find the last column with data in it. Perhaps not useful if columns beyond your data need to be dealt with and you need to cover that possibility. Otherwise one way
Lcol = .Cells.Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column

However, I'm not smart enough to know when to use xlPrevious or xlNext but what I garnered from other sources seemed to work when I was attempting to help others. I'm not an Excel vba guru by any means.

 

[spacecaptainsuperguy]

What I showed you was specific to everyone's means as long as you had the helper columns. It's the approach I would take as I'd rather let someone edit their choices on a sheet rather than have to delve into each person's code to make alterations for them.

I must have misunderstood the requirement then. I interpreted the requirement as "if anything is hidden, unhide it, then hide the columns I want hidden". According to how I interpret your code, that won't happen but now it seems that is not a requirement.

As for looping over 16K+ columns, carry on with that if you're happy with what you have. However, it is not too difficult to find the last column with data in it. Perhaps not useful if columns beyond your data need to be dealt with and you need to cover that possibility. Otherwise one way
Lcol = .Cells.Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column

However, I'm not smart enough to know when to use xlPrevious or xlNext but what I garnered from other sources seemed to work when I was attempting to help others. I'm not an Excel vba guru by any means.

I don't think you misinterpreted what I was originally looking for. Ideally it would have been press the button once to get the view I want and then again to unhide all columns but doing so, at least to my limited way of thinking, would mean that the macro would have to "capture" what the last button press was in order to know what to do next and I don't know that there is an easy way to do that.

and to your comment:

I'd rather let someone edit their choices on a sheet rather than have to delve into each person's code to make alterations for them.

That's completely valid. It's not the approach I was going for but I will explore it because there is always something new to learn.

I've done stuff in the past to find the last row and the last column but I figured that would then also need to incorporate a loop and I'm not great with coding that on my own at this point, but that is also a way I might try to "tighten it up".

As always, I'm super grateful for everyone input on this board.

 

[Micron]

I don't think you misinterpreted what I was originally looking for

Then perhaps I should add:
Then I'm not seeing what you're seeing. What I think you have, in words:
- loop over all row 1 cells in the sheet
- if column hidden, add 1 to counter
- then unhide that column
- rinse and repeat for all columns (16+K)
So if there were 3 hidden, counter =3

Next, see if counter = 0
If True, hide these columns (FF, GG and so on)
Since counter = 3, don't hide these columns and just end sub.

So when columns were at first hidden then unhidden, no columns are subsequently hidden, which I thought was always supposed to happen when you toggle. I suppose you could test that scenario if you have not done so already but I see you ending up with no hidden columns if any were hidden to start with. Perhaps I'm missing something.

the macro would have to "capture" what the last button press was in order to know what to do next

That's where variables with the proper scope can help. I hesitate to use the words "global variable" since that means they are accessible to any code in the project and thus are susceptible to unwanted alteration. In your case, it may be sufficient to dim the variable in the declarations section of the module that contains your code. Often, a boolean is used; if True do this, if False do that. Note that booleans are false by default, which means the first time you examine it in an IF block, its value may not be suitable.

 

[lilgto64]

I don't think you misinterpreted what I was originally looking for. Ideally it would have been press the button once to get the view I want and then again to unhide all columns but doing so, at least to my limited way of thinking, would mean that the macro would have to "capture" what the last button press was in order to know what to do next and I don't know that there is an easy way to do that.

and to your comment:

That's completely valid. It's not the approach I was going for but I will explore it because there is always something new to learn.

I've done stuff in the past to find the last row and the last column but I figured that would then also need to incorporate a loop and I'm not great with coding that on my own at this point, but that is also a way I might try to "tighten it up".

As always, I'm super grateful for everyone input on this board.

Finding the last used cell is always a pain - because the built in function will include the last cell that was touched before you delete lots of rows/columns. But at least it can prevent you from having to check EVERY row/column.

lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

in a lot of places I use something like this:

Range("O60000").End(xlUp).Select ' replace O60000 with what ever column you know you have data in and a row number high enough that it is past what you are likely to use

DataAddRow = ActiveCell.Row

If you have labels in the the first column you could also do something like:
Set FoundLabel = Cells.Find(what:="Label")
If Not FoundLabel is Nothing then
lastrow = FoundLabel.row
end if

 

[spacecaptainsuperguy]

Micron, to your comment: but I see you ending up with no hidden columns if any were hidden to start with
That is correct.
I think the part you are missing, is that if for example I open the spreadsheet and some of the columns are hidden that I need unhidden then the first thing I need to make sure happens is that all columns are unhidden. The for loop does that part and if any are hidden it adds to the counter, which will cause it to skip the If statement and not hide anything. If I run the macro again, the For loop finds no hidden columns so nothing gets added to the counter and the If statement hides my particular columns. If I run it a 3rd time, it will find my hidden columns, add to the counter and skip the if. If someone else opens the spreadsheet and nothing is hidden, one run of the macro and it will hide their particular columns. If anything was hidden, it will unhide them and then on the 2nd run it would hide their particular columns.

The less than ideal part is that it may take 2 runs of the macro to get what you want to see, but it is still nevertheless toggling between unhide all and hide what I want. Not perfect but at this stage, close enough.

 

[Micron]

If I run the macro again

Now I understand your approach. Mine is to do everything in one pass - unhide all, then hide what needs to be hidden. No need to run again to achieve the goal. Only need to run to redo the same hiding (if it was altered) or change the hidden. Thanks for the explanation.

ActiveSheet.UsedRange.

As for last row or column, I think it is widely accepted to not use UsedRange since that includes empty ranges that have been formatted - unless of course you want to deal with formatting in the procedure. Just so you know.