- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to hide columns on multiple sheets in a workbook in Excel?
Excel allow user to rapidly hide columns within a single worksheet, but have you ever attempted to hide columns across multiple sheets at once? Normally, if you attempt to select all sheets and use the Hide feature, it won't work. In this article, I'll discuss how to quickly hide columns across numerous worksheets.
It may be desirable to show or print a worksheet so that some of the data is obscured but left intact in the workbook. For hiding private or confusing information, one can hide rows or columns. In this article, an example is demonstrated, to hide columns on multiple sheets in a workbook by using VBA codes.
Hide columns on multiple sheets in a workbook by using VBA Codes
Create a sample data to understand the processing step. Use column C to write student name, column D to write subject name, and column E to write marks.
To open the visual basic code area, click on Developer Tab under the code group and choose Visual Basic option, as highlighted in the below given image.
Note − To access the Microsoft Visual Basic for Applications window, another method is to hold down the ALT + F11 keys equally. This will also open the same code area.
The above step will open a “Microsoft Visual Basics for Applications” dialog box, as specified below −
In the above opened dialog box, click on “Insert” Tab. This will open a new drop-down list. In the new window, select “Module” option as depicted below −
After clicking on Module, visual basic code area will be displayed on appeared dialog box. Consider image for proper reference −
In the opened code editor, copy the below given code −
' define meth_to_hide_Column() method header Sub meth_to_hide_Column() ' declaring required variables Dim i As Integer Dim col_s As String On Error Resume Next ' define input box col_s = Application.InputBox("Enter column range to hide,Eg A:A OR A:B", _ "VBA code block result", , , , , , 2) ' if column If col_s = "" Then ' display message MsgBox "Empty columns", vbInformation, "Enter valid data columns!!!" 'end of sub block Exit Sub ' end of if block End If ' for each loop expression For i = 1 To ThisWorkbook.Worksheets.Count ' setting column value to hidden ThisWorkbook.Sheets(i).Columns(col_s).Hidden = True ' next statement Next i ' use go to loop On Error GoTo 0 ' end of sub block End Sub
Code snapshot will be provided below −
Now Press F5 Key to run the code, a Macros dialog box will appear on Microsoft excel as shown below in the image.
After that click on Run button as highlighted in the below given image −
The code will display a “VBA code block result” dialog box on screen. This dialog box contains input label to enter the label in the provided format that user wants to hide. In this example, user want to hide “C” column. Finally, click on the “OK” button of the dialog box. Please note that in the example, user provides input for a single column, but, multiple input column is also acceptable. Suppose if user input A:C, then all the column from A to C will be hidden, similarly provided in the output of the next step.
After the completion of all the steps, C column will hide automatically. Consider below given output snapshot for reference.
In the article the user learnt the strategy to implement the VBA code to hide the columns from multiple sheet. The provided steps are detailed and thorough. All the provided details are accurate and precise.
Kickstart Your Career
Get certified by completing the courseGet Started