Unhiding a Limited Number of Columns

@Allen
Yes, you do appear to have missed something, or at least complicated the use of Grouping to provide a solution to Malcolm.

In the situation you describe, as far as I can tell from Malcolm's original request for help, he would have no problem with an existing range of hidden columns (say F:J) being expanded to include a couple more (K:L) thus creating a larger contiguous range of hidden columns (F:L).

If he then wishes to unhide any subgroup of columns within that new expanded range, he just clicks the + button to reveal those hidden columns, then selects any contiguous range therein, then selects the Ungroup command. He will then have two separate grouped column ranges (one group either side of those he ungrouped), which he can readily collapse either by clicking the - symbol above each, or clicking the outline level number in the top left of the grid. Unfortunately, Excel doesn't allow you to select and Group/Ungroup non-contiguous columns, so separate iterations are required.

So if Malcolm has 100 columns hidden and wants to unhide just the last 10 columns of that 100, he expands the range by clicking the + symbol in the upper border, selects the last 10 columns, clicks the Ungroup command, then clicks the - symbol to "hide" the remaining grouped columns. Simple!

PS: If you use Grouping as often as I do, you'll add the Group and Ungroup buttons to your Quick Access Toolbar.

Did you ever look into a mirror and raise your right hand, then wonder why that guy was raising his left?
Maybe Shadeburst has a point. He can modify part of my UnhideLastCols macro as follows:
    ...
    Dim firstCol as Integer
    ...
        For n = lastCol To 1 Step -1
            firstCol = n
            If .Columns(n).Hidden Then
                number = number - 1
                If number = 0 Then Exit For
            End If
        Next n
        .Range(Columns(firstCol), Columns(lastCol)).Hidden = False
    ...

@Shadeburst
Because you need to count the number of hidden columns you unhide. If you simply unhide the last N columns, you would not need a macro. If you unhide the last N columns, how many hidden columns did you unhide?

Why does everyone write the code as "if column hidden then column hidden = false end if". Just say column hidden = false. If it wasn't hidden what difference will it make? Yeah no discernible difference in execution time even if you looped it to run ten million times, but writing economical code is a good habit.

@ J. Woolley, just FYI, on the current (365) Excel version, the button group on the ribbon is called Outline, but the buttons themselves are still "Group" and "Ungroup". In many cases I see users who disable the button group names on the ribbon (which on the Mac is more or less the default even).

Both Col and Rene mentioned using grouping to "hide" columns. However, in my testing I could not make this approach work properly for Malcolm's original needs.

Try this: Select 5 columns, let's say columns F:J, and group them. The plus sign appears as you would expect, indicating that the columns are now in a group.

Now select two ADJACENT columns, let's say columns K:L, and group them. Excel doesn't make these two columns a new group; it extends the original group. At this point you can only hide F:L or unhide F:L. You cannot click the small plus sign to hide/unhide K:L independent of F:J.

Which means grouping cannot be done to help Malcolm. He has 100 columns hidden and wants to unhide just the last 10 columns of that 100. Try as I might, I could not get grouping to accomplish this.

Now, I admit I could be missing something, and I also admit that grouping has its advantages for some purposes, but it doesn't seem to work for Malcom's purposes. This is why I didn't include it in the tip as a possible solution.

-Allen

Don't Hide columns in the first place - doing so is nearly as bad as using the Merge & Centre command - as hidden columns (and rows for that matter) are difficult to spot in a worksheet, and cumbersome to unhide a subset therein!

A better option is to Grouping as this will suppress the display of the selected columns/rows whilst also displaying a + symbol in the border. You can then simply click the + symbol to expand (display) the hidden columns/rows. If you then wish to "unhide" a subset of the Grouped columns/rows, just select them and click the Ungroup command.

Simple , quick and easy - and far less dangerous.

Here is another macro that might be more versatile because it makes no assumptions about the content, arrangement, or number of hidden columns. It is more efficient than the Tip's macro because it considers the worksheet's UsedRange, which includes hidden columns (and rows).

Sub UnhideLastCols()
    Dim n As Integer, number As Integer, lastCol As Integer
    Const prompt = "To unhide the last N hidden columns, enter N:"
    Const title = "UnhideLastCols"
    number = Application.InputBox(prompt, title, 0, Type:=1)
    If number < 1 Then Exit Sub
    With ActiveSheet
        With .UsedRange ' includes hidden columns
            lastCol = .Column + .Columns.Count - 1
        End With
        For n = lastCol To 1 Step -1
            If .Columns(n).Hidden Then
                .Columns(n).Hidden = False
                number = number - 1
                If number = 0 Then Exit For
            End If
        Next n
    End With
End Sub

Like most macros, this macro does NOT support Undo (Ctrl+Z); it could be modified to add such support, but doing that properly is nontrivial.
See https://sites.google.com/view/MyExcelToolbox/

@Rene
I like your approach, but I believe newer versions of Excel label the ribbon choice like this: Data > Outline

Instead of hiding columns you could also group them (data, group).

I originally offered the following option, but this seems to have been ignored.
I believe that it would be much more flexible, since it allows the user to select any random number of columns to be unhidden.
The columns do not need to be grouped together.

What is needed here is a method to 'mark' the columns that you wish to unhide.

There are several ways of doing this but I have chosen to set the cell in Row 1 to have a font colour of RED.
This is the easiest colour to set, since it is normally a default option on the ribbon.
Alternative options could include setting the font size or font type to a unique value.

My macro has the advantages of :-
1) You can easily change the columns that are affected at any time by just changing the font colour.
2) Can be applied to any random selection of columns. They do not need to be next to each other.
3) If you insert or remove columns between the 'marked' columns then the columns that you wish to unhide are not affected.

There is one slight disadvantage. the macro depends upon finding the last active column, but a hidden column cannot be active.
Therefore there MUST be at least one column with data present after the last hidden column.
This could just be cell 1 with a title included. If you do not want to see this then just set its font colour to WHITE, making it invisible.

Macro below
==========

Sub Unhiding()

' Unhiding Macro - recorded April 2022 by Graham Rice
' Columns defined by cells in FIRST row having a font colour of RED (255)

Dim active_column, currentcell

'To run faster & stop screen flicker, especially for large number of columns.
Application.ScreenUpdating = False
Application.Calculation = xlManual
ActiveWorkbook.PrecisionAsDisplayed = False

Set currentcell = ActiveCell 'Stores location of current "active" cell

Selection.SpecialCells(xlLastCell).Activate
active_column = ActiveCell.Column
'Starts search in last column currently in use.
'Must be AT LEAST one active (data present) column visible after the last hidden column.
Do While active_column > 0
If Cells(1, active_column).Font.Color = 255 Then
'Any cell in top row with font set as RED
Cells(1, active_column).EntireColumn.hidden = False
End If
active_column = active_column - 1
Loop

currentcell.Select ' Re-establishes previous "active" cell position

'Finally reset screen updating
Application.Calculation = xlAutomatic
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub

You can do it easily without a macro:

- type in the Name Box the columns you want to unhide e.g. x:z
- in the home tab, Cells section, select Format | Hide & Unhide | Unhide Columns
- the columns X, Y and Z are now visible

For unhiding non adjacent columns, type e.g. m:m,t:s in the Name Box: this unhides columns M, S & T