Unhiding a Limited Number of Columns

Written by Allen Wyatt (last updated April 17, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


12

Malcolm hides columns all the time in a worksheet he uses daily. He wonders if there is a way to unhide just a portion of the hidden columns. For instance, if he has 100 hidden columns, he may want to unhide only the last 10 of those columns.

The answer depends on the nature of the data you are working with. If the columns you need to unhide are always the same ten columns, then you could use a macro to do the unhiding. For instance, if you always want to unhide columns DA:DJ, then this single-line macro will work:

Sub UnhideCols()
     Columns("DA:DJ").Hidden = False
End Sub

If the columns you want to unhide will change, then you could modify the macro to ask the user to enter the column range:

Sub UnhideCols()
    Rng = InputBox("Unhide which columns? (x:y)")
    Range(Rng).EntireColumn.Hidden = False
End Sub

Of course, if you are going to enter a range of columns, you could skip the macro entirely by following these steps:

  1. Click once in the Name box, just to the left of the Formula bar.
  2. Type the range you want to unhide, such as DA:DJ, and then press Enter. Excel selects the columns, even though they are hidden.
  3. Display the Home tab of the ribbon.
  4. Click Format | Hide & Unhide | Unhide Columns. Excel unhides those columns.

If your worksheet only has a single block of hidden columns and you don't know exactly where that block might be, then you could use the following macro to unhide just the last 10 hidden columns:

Sub UnhideLast10()
    Dim J As Integer
    Dim K As Integer

    K = 0
    For J = 16384 To 1 Step -1
        If Columns(J).Hidden Then
            Columns(J).Hidden = False
            K = K + 1
        End If
        If K = 10 Then Exit For
    Next J
End Sub

The macro starts looking at the rightmost column and steps toward the left. If it finds a hidden column, it unhides it and increments the K counter. When K reaches 10 (which means 10 columns have been unhidden), then the macro is automatically exited.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12869) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

An Unruly Ruler

Does the ruler in Word seem to have a mind of its own? If you have problems understanding why the ruler behaves as it ...

Discover More

Adding Serial Commas in a Sentence

Part of the job of an editor is to apply standards of grammar to text written by someone else. One standard that may need ...

Discover More

Counting Words

Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Widening a Column to a Particular Cell's Width

Do you want to set a column's width based on whatever is in the currently selected cell? There are actually a number of ...

Discover More

Unhiding Columns that are Persistently Hidden

If you were trying to format a worksheet and nothing you did could make the first two columns appear, would you be ...

Discover More

Widening Multiple Columns Proportionally

It is easy to adjust the width of columns in Excel. It is much harder to adjust the width of a range of columns ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 0?

2022-04-20 20:30:29

Col Delane

@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.


2022-04-20 10:48:44

J. Woolley

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
    ...


2022-04-19 10:22:25

J. Woolley

@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?


2022-04-19 03:34:19

Shadeburst

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.


2022-04-18 23:49:03

Philip

@ 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).


2022-04-18 07:42:58

Allen

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


2022-04-18 04:09:05

Col Delane

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.


2022-04-16 13:35:43

J. Woolley

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/


2022-04-16 12:49:28

J. Woolley

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


2022-04-16 06:46:06

Rene

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


2022-04-16 06:06:59

Graham

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


2022-04-16 05:47:10

Willy Vanhaelen

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


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.