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

Question

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.

Answer 1

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