For various reasons, we hide columns and rows in Excel. At the same time, we may need to unhide them for our needs. Unhiding them may involve various types of criteria and could be a little bit challenging. If you are curious to know how you can unhide all columns in Excel using VBA, then this article may come in handy for you. In this article, we discuss how you can unhide all columns in Excel using VBA with an elaborate explanation.

Download Practice Workbook

Download this practice workbook below.

8 Suitable Examples to Unhide All Columns in Excel Using VBA

For the demonstration purpose, we will use the below dataset. In almost all cases, we will first hide the columns and then apply various VBA codes to unhide them automatically. These methods may need a variety of criteria to handle.

1. Unhide All Columns in a Sheet

We can use the below VBA code to unhide the entire columns in a single worksheet.

Steps

• We can clearly see from the image below those columns B, C, D, E, and F are in a hiding state.
• To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, in the Module editor window, type the following code:

Sub Unhide_Columns_in_Excel()
Columns.EntireColumn.Hidden = False
End Sub

• Then close the Module window.
• After that, go to View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the Macros that you created just now. The name here is Unhide_Columns_in_Excel. Then click Run.

• After clicking Run, you will notice that the hidden columns are back where they used to be.

2. Unhide All Columns in Whole Workbook

We can use the below VBA code to unhide the entire column in a single workbook. This VBA code can access data from a different worksheet and unhide columns simultaneously.

Steps

• We can clearly see from the image below that columns C, and E are hidden in the Unhide Columns in Workbook.

• You can also see that columns C and F are hiding in the Unhide Columns in a Sheet worksheet.
• We need to Unhide all of them together using a VBA Macro.

• To insert a VBA Macro, go to the Developer tab, then click on Visual Basic from the Code group.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, in the Module editor window, type the following code:

Sub Unhide_Columns_in_Whole_Workbook()
Dim x As Worksheet
For Each x In Worksheets
x.Columns.EntireColumn.Hidden = False
x.Rows.EntireRow.Hidden = False
Next x
End Sub

• Then close the Module window.
• After that, go to View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the Macros that you created just now. The name here is Unhide_Columns_in_Whole_Workbook. Then click Run.

• After clicking Run, you will notice that the hidden columns are now unhidden from both the Unhide Columns in Workbook and Unhide Columns in Sheet worksheet.

3. Unhide Specific Contiguous Columns

If your worksheet contains columns that are contiguous, meaning the columns that are hidden are stacked one after another. Then we can use the below VBA code to unhide the specific contiguous columns more efficiently.

Steps

• We can clearly see from the image below those columns B, C, and D are being hidden.
• All of them are contiguous. So, a simple VBA Macro could easily unhide them altogether.
• To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, in the Module editor window, type the following code:

Sub Unhide_Contiguous_Columns()
Worksheets("Specific Contiguous Columns").Range("B:D").EntireColumn.Hidden = False
End Sub

• Then close the Module window.
• After that, go to View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the Macros that you created just now. The name here is Unhide_Contiguous_Columns. Then click Run.

• After clicking Run, we will notice that columns B, C, and D are now in unhide state.

4. Unhide Specific Non-Contiguous Columns

If your worksheet contains columns that are not contiguous, meaning the columns that are hidden are intermittently irregularly spaced from each other, then we can use the below VBA code to unhide the specific non-contiguous columns more efficiently.

Steps

• We can clearly see from the image below those columns B, C, and E, F is hiding in the worksheet.
• All of them are contiguous in the group. A simple VBA Macro could easily unhide them altogether.
• To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, in the Module editor window, type the following code:

Sub Unhide_Non_Contiguous_Columns()
Worksheets("Specific Non-Contiguous Columns").Range("B:C,E:F").EntireColumn.Hidden = False
End Sub

• Then close the Module window.
• After that, go to View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the Macros that you created just now. The name here is Unhide_Non_Contiguous_Columns. Then click Run.

• After clicking Run, you will notice that the non-contiguous hidden columns are now unhidden from the sheet Specific Non-Contiguous Columns.

5. Unhide Columns in Specific Range

If your worksheet contains hidden columns, that are hidden serially. At the same time, if those numbers of columns are also pretty high that typing them inside a VBA code is a hassle. Then you can use the below VBA code to unhide those hidden columns altogether in a specific range.

Steps

• We can clearly see from the image below that entire columns B, C, and D are being hidden.
• All of them are actually in serial. A simple VBA Macro could easily unhide columns altogether.
• To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, in the Module editor window, type the following code:

Sub Unhide_Specific_Range()
For i = 2 To 4
Columns(i).Hidden = False
Next i
End Sub

• Then close the Module window.
• After that, go to View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the Macros that you created just now. The name here is Unhide_Specific_Range. Then click Run.

• After clicking Run, you will notice that Columns B, C, and D are now unhidden.

6. Unhide Columns Based on Cell Value

In this case, the VBA code will search for a particular value in the hidden columns in the worksheet. Then if any cell matched with that particular value, the code will unhide the column in which the cell resides in the worksheet.

Steps

• We can see from the image that, a value of 35 is in cell E5.
• We are going to unhide the columns based on this value. This means that if there is any value in the hidden column that matches the value 35, the column will be unhidden.

• To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.
• We can also notice that columns C and E are hidden.

• Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
• Next, in the Module editor window, type the following code:

Sub Unhide_Column_Cell_Num_Value()
StartingColumn = 2
EndingColumn = 5
irow = 5
For i = StartingColumn To EndingColumn
If Cells(irow, i).Value = 35 Then
Cells(irow, i).EntireColumn.Hidden = False
End If
Next i
End Sub

• Then close the Module window.
• After that, go to View tab > Macros.
• Then click on View Macros.

• After clicking View Macros, select the Macros that you created just now. The name here is Unhide_Column_Cell_Num_Value. Then click Run.

• After clicking Run, you will notice that the column containing value 35 is now unhidden.
• And the rest of the hidden columns stay hidden.