How to hide columns on multiple sheets in a workbook?
In Excel, we can quickly hide columns in one worksheet, but, have you ever tried to hide columns across multiple sheets at once? Normally, you may try to select all sheets and apply the Hide feature, but it will not work successfully. This article, I will talk about how to hide columns across multiple worksheets quickly?
The following code can help you to hide specific columns across all worksheets immediately, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Hide columns on multiple sheets at once:
Sub Hide_Columns() 'Updateby Extendoffice Dim iWs As Integer Dim sCol As String On Error Resume Next sCol = Application.InputBox("Input entire column,Eg A:A OR A:B", _ "Kutools for Excel", , , , , , 2) If sCol = "" Then MsgBox "Empty columns", vbInformation, "Kutools for Excel" Exit Sub End If For iWs = 1 To ThisWorkbook.Worksheets.Count ThisWorkbook.Sheets(iWs).Columns(sCol).Hidden = True Next iWs On Error GoTo 0 End Sub
3. Then press F5 key to run this code, and a prompt box is popped out to remind you type the columns that you want to hide across all worksheets, see screenshot:
4. And then click OK button, your specified columns have been hidden in all worksheets at once.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoHow do I do this for rows?
- To post as a guest, your comment is unpublished.· 2 years agoAnd the VBA for unhiding the multiple columns as well.
Thank you in advance and God bless
- To post as a guest, your comment is unpublished.· 2 years agoHi, Treb,
To show all the hidden columns from the whole workbook, please apply the below VBA code:
Dim iWs As Integer
Dim sCol As String
On Error Resume Next
For iWs = 1 To ThisWorkbook.Worksheets.Count
ThisWorkbook.Sheets(iWs).Columns.Hidden = False
On Error GoTo 0
Please try, hope it can help you!
- To post as a guest, your comment is unpublished.· 2 years agoI try to use this VBA code that you made to hide multiple columns in a multiple worksheets.. But How can I use it with out a message Box...
Please kindly help me to make a VBA that will hide the multiple columns in all worksheets at once with out having a lot of message box?..
I badly needed such a code.
- To post as a guest, your comment is unpublished.· 2 years agoHello, Treb,
The popped out message box can help you to enter the hidden columns you need conveniently and dynamically.