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
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:
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:
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.
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!
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 MoreIf you were trying to format a worksheet and nothing you did could make the first two columns appear, would you be ...
Discover MoreIt is easy to adjust the width of columns in Excel. It is much harder to adjust the width of a range of columns ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments