How to save the range of all hidden columns in VBA for Excel

1
votes

In VBA for Excel, I need to save the range of all columns in the sheet that are hidden, unfortunately I don't manage to find out how to do this. To put more context, my intent is to save the range of hidden columns of the sheet in a temporary variable, then unhide the columns, save the workbook and re-hide the saved columns, so that the workbook is always saved with all columns visible. I am stuck on the "save the range of hidden columns of the sheet in a temporary variable" step. Thanks for you help.

2
excelvbarangehidden
Hi, first share with us any code you've tried. Next, I would look at this as 3 steps. 1. List all headers. 2. Identify if they are visible 3. If they are not visible, add them to an array. Then the last step is to create the sub that actually un/hides the array of headers. – Mark S.
@MarkS.Here I need to get the range of hidden columns. Then it is possible to run something like mRange.EntireColumn.Hidden = False – Philippe

2 Answers

0
votes

There might be a more efficient way to achieve what you want, but one way would be to loop through the columns of your range, and if the column is hidden then add it to a range variable using Union.
For example, let's say that you want to store all hidden columns from a variable mInitialRange into a variable mHiddenColumns. This would give you :

Dim mInitialRange As Range, mHiddenColumns As Range
For Each mcolumn In mInitialRange.Columns
    If mcolumn.Hidden Then
        If mHiddenColumns Is Nothing Then
            Set mHiddenColumns = mcolumn
        Else
            Set mHiddenColumns = Union(mHiddenColumns, mcolumn)
        End If
    End If
Next mcolumn

Edit: Improved following @BigBen advices

0
votes

It's actually a pretty simple process. In the future you really need to share with us what you've done to try to solve the problem.

I assume you're fairly new to vba to be asking this, please see the comments I left in the below code.

    Sub runme()
Dim HiddenColumns(), size As Integer
    'Using a seperate counter, loop through your range of data.
    'If you find a hidden column redim our hidden columns array and add that row's number to the array
    'then increase our seperate counter
    size = 0
    For i = 1 To 12 'Change this to your range
        If Columns(i).Hidden = True Then
            ReDim Preserve HiddenColumns(size) 'Redim and preserve the array to our "size" variable (which will always be one more than the current array size
            HiddenColumns(size) = i
            size = size + 1
        End If
    Next i

    'Now we want to loop through our array and flip all the columns that were hidden to shown
    'You can add this to the original array building loop to be more efficent I'm just breaking it up here
    'for demonstration purposes
    For i = 0 To size - 1
        Worksheets("Sheet1").Columns(HiddenColumns(i)).Hidden = False
    Next i

    'Call your workbook saving here
    ThisWorkbook.Save

    'Now loop through our array of columns once more to rehide them
    For i = 0 To size - 1
        Worksheets("sheet1").Columns(HiddenColumns(i)).Hidden = True
    Next i
End Sub