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.
Unhide All Columns in Excel VBA: 8 Suitable Examples
For demonstration purposes, 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 that 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 the 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 the 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 contiguous columns, 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
💬 Note
In the VBA code, you need to specify the range in which your contiguous columns are hiding. Not only that, but you also need to specify the worksheet name in the code.
- Then close the Module window.
- After that, go to the 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 an 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 that columns B, C, and E, F are 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
💬 Note
- The range of columns that are hiding will be separated by “,”.
- Not only that, but you also need to specify the sheet name in the code.
- Then close the Module window.
- After that, go to the 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 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 the 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.
🔎 VBA Code Breakdown
- In the beginning, we provide a name for the sub-procedure which is Unhide_Specific_Range.
- After that, we used a VBA For Loop to check column numbers and then hide them.
- Finally, end the sub-procedure of the code.
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 matches 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
💬 Note
- The code only checks one row at a time for the match value.
- The user thus needs to modify and change the irow value in the code if the user wants to check for the other rows.
- Then close the Module window.
- After that, go to the 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.
🔎 VBA Code Breakdown
- First, provide a name for the sub-procedure which is Unhide_Column_Cell_Num_Value.
- Then, we declare the first and last variable column names for our dataset: StartColumn, EndingColumn, irow.
- After that, we used a VBA For Loop to check from the StartColumn to EndingColumn. Each loop will create a cell address.
- In addition, we used a conditional line to check whether the cell value is equal to a particular value or not. If that value is equal to that particular value, then the following line will unhide the column of that cell.
- Finally, end the sub-procedure of the code.
- That is how we unhide columns based on a cell number in Excel VBA.
7. Unhide Columns Based on Text Value
In this case, the VBA code will search for a particular text in the hidden columns in the worksheet. Then if any cell matches 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 text value of “South Asia” is in cell D5.
- 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 text value “South Asia”, the column will be unhidden.
- To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.
- At the same time, we can notice that Columns B and D are hidden right now.
- 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_Text_Value()
StartColumn = 2
LastColumn = 5
irow = 5
For i = StartColumn To LastColumn
If Cells(irow, i). Value = "South Asia" Then
Cells(irow, i).EntireColumn.Hidden = False
End If
Next i
End Sub
💬 Note
- In the code, it only checks one row at a time for the match value.
- The user thus needs to modify and change the irow value in the code if the user wants to check for the other rows.
- Then close the Module window.
- After that, go to the 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_Text_Value. Then click Run.
- After clicking Run, you will notice that the column containing the text value “South Asia” is now in an unhide state.
- And the rest of the hidden columns stay hidden.
🔎 VBA Code Breakdown
- First, provide a name for the sub-procedure which is UnHide_Column_Cell_Text_Value.
- Then, we declare the first and last variable column names for our dataset: StartColumn, EndingColumn, irow.
- After that, we used a VBA For Loop to check from the StartColumn to EndingColumn. Each loop will create a cell address.
- In each, we use a conditional line to check whether the cell value is equal to a particular text value or not. If that value is equal to that particular value, then the next line will unhide the column of that cell.
- Finally, end the sub-procedure of the code.
- That is how we unhide columns based on a text value in Excel VBA.
8. Unhide All Columns with Message Box
You can check whether any column is hiding in a range of cells. Then this VBA code may come in handy for you. After checking whether the columns are hidden are not, it will unhide the hidden columns in the worksheet. At the same time, it will show a message box about the status of columns.
Steps
- 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 With_Messege_Box()
With ActiveSheet
If .Columns("C:E").EntireColumn.Hidden Then
MsgBox "Invisible"
.Columns("C:E").EntireColumn.Hidden = False
Else
MsgBox "Selected Columns are Accessible"
End If
End With
End Sub
- Then close the Module window.
- After that, go to the View tab > Macros.
- Then click on View Macros.
- After clicking View Macros, select the Macros that you created just now. The name here is With_Messege_Box. Then click Run.
- After clicking Run, the VBA code first checks whether there is a column hidden in column range C: E. If any of them is hidden, there will be a small box saying “Invisible”.
- Click OK.
- Then it will unhide the columns.
- If the range of Columns C: E is not hidden, then the “Selected Columns are Accessible” message will be shown.
- They will look like the below image.
- And this is how we unhide all columns with Excel VBA with a message box.
🔎 VBA Code Breakdown
- First, provide a name for the sub-procedure which is With_Messege_Box.
- In the next line, With ActiveSheet will denote that the code will execute only in the currently selected worksheet.
- After that, we used a VBA IF Loop to check whether the denoted column was hidden or not. If it is hidden, then there will be a small box saying “HIdden”. Otherwise, it will show that “Those columns aren’t hidden”
- If the columns are hidden state, Columns(“C:E”).EntireColumn.Hidden = False will directly unhide the columns.
- Finally, end the sub-procedure of the code.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, with 8 different examples, we solve here the issue of unhiding all the columns in Excel VBA. The VBA Macro method requires prior VBA-related knowledge to understand from scratch. For this problem, a Macro-enabled workbook is available for download where you can practice these methods.
