Can VB Save hidden/grouped columns state, unhide all, then restore columns to saved state

Similar to custom views but not custom views.

I have some code, that to work properly, requires columns to be unhidden:

I have the following code so far for unhiding/rehiding:

Please Login or Register  to view this content.

It unhides all hidden columns/opens all grouped columns in the spreadsheet, my other code runs, then it closes all grouped columns in the spreadsheet.

There are a couple of problems with this though:

1. If the user actually hides columns rather than using the outline/grouping feature in excel then at the end of the macro their originally hidden columns will be left unhidden.
2. If they have multiple levels of grouping it closes all grouping to level 1. The user may want some groups open most of the time while others closed all of the time. While I can close to different levels there is no way for me to know what levels or groups the user wants open/closed so by default the best thing to do is to close them to level 1. I am trying to avoid having the user have to go and put his grouping back the way they like it after running the macros.

What I really want to do is the following:

Please Login or Register  to view this content.

So it would act similar similar to what custom views does except without the other settings. Custom views doesn't work for me as:
1. I do not want to save and restore filter settings which custom views forces me to do
2. Custom views messes up the frozen panes.

So unless custom views can be applied to columns only or without the filter settings it is not a working solution

To better understand review the attached workbook and step through the code: You'll notice that at the end the columns are not shown how they were originally when the workbook was opened


See this thread for background/the other code driving this requirement: http://www.excelforum.com/excel-prog...all-cells.html

This code seems to work:

Please Login or Register  to view this content.

Wow, that is amazing, as far as i can tell it works flawlessly, and it's fast. I combined it with my code from the other thread that inserts work packages and there was no noticeable slowdown or issues or anything. Columns and grouping were restored to exactly how they were before running the macro and the inserting of new rows and copying and pasting ranges went off without a hitch. Now i can have autofilters on and not have to screw up users hidden column settings. This is an awesome solution to my problem in my other thread. Thank you so much.

I do have a couple questions(hopefully quick ones) if you don't mind:

1. Can you give me a little info as to what is going on here. Is it looping through all ~16K columns in the spreadsheet and saving their hidden state to memory or an array, or is it checking a smaller range determined by some thing.

2. What are the option base 1 and ReDim Columnstate(1) statements, I haven't seen these before. A quick google tells me that optionbase has something to do with setting the lower bound of an array but i wasn't clear on how it determined that/worked or what the "1" was for.

3. Are there any specific resources you could point me to that explain how to do things like this. I've recently started learning VBA in bits of free time at work and most of what i've learned i've learned from the macro recorder, browsing the VBA object browser and by stepping through and "reverse engineering" other peoples vba code to see how i could apply it to my needs. Which is good for simple stuff but the more complex stuff eludes me. There are so many resources and articles out there that it's hard to tell which ones are qualy or best for learning & spending time on, it's easy to get lost in it all

4. I will be using this code in multiple macros that have to select and work with the large ranges. Do i need to release the dimensions/variables at the end of the code to not eat up memory or anything ie:

Please Login or Register  to view this content.

1a. Because I could not tell from the sample you gave me how may columns are actually being used (no headers,etc), the macro checks all columns of the sheet.

1b. It checks the hidden state. If the column is hidden, it saves the column number to an array (note that columnstate is just a name I chose; it is not a VBA keyword).

2a. Normally, when you declare an array, say, a ten element array, the first element as an address of zero, and the last would be nine. Option Base 1 forces the elements to be numbered one through ten instead.

2b. Because I did not know exactly how many columns you are actually using, I declared the array without specifying any dimensions. Before using the array, I forced it to have only one element with:
ReDim columnstate(1), the "1" being the number of elements.

2c. The For Each...Next loop then checks the column's hidden state and, if that particular column is hidden, it saves the column number in the last element of the array. Note that since I forced the array to have only one element to start, the first hidden column number goes there. Then I use: ReDim Preserve columnstate(UBound(columnstate) + 1) to add another element to the array; the Preserve causes the redimension to keep all current data. When the last column has been checked, the array will have an empty element at the bottom, so I used: ReDim Preserve columnstate(UBound(columnstate) - 1) to bump down the number of elements by one.

3. I don't think I can help you with any good books.

4. VBA cleans up after macros terminate so setting variables "=Nothing" isn't necessary (as far as I know).

Option Base 1 is (just) my preference when working with rows and columns, since there is no row/column zero (in this case it doesn't matter). Another alternative is:

Please Login or Register  to view this content.

In your second example, redim'ing to 2 means that the first element will always be empty since it will never be accessed. That will work but I would prefer testing to see if there is more than one.

I tried that but then found i got an application/object defined 1004 error on

Please Login or Register  to view this content.

in the for cCount...statement

I'm guessing that is due to trying to restore an array that isn't there

Trapping it with the same If UBound(columnstate) > 1 Then...End If works unless you have only 1 column hidden.

My solution to that ended up being this:

Please Login or Register  to view this content.

Just captured the value of UBound(columnstate) to another variable lngHasColH and used that in the if conditions

I think i will probably go with just setting option base to 0 though since that minimizes the amount of code and will hopefully keep things faster. Learned a lot messing with it though.