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

Step 1

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.

Step 2

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.

Step 3

The above step will open a “Microsoft Visual Basics for Applications” dialog box, as specified below −

Step 4

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 −

Step 5

After clicking on Module, visual basic code area will be displayed on appeared dialog box. Consider image for proper reference −

Step 6

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 −

Step 7

Now Press F5 Key to run the code, a Macros dialog box will appear on Microsoft excel as shown below in the image.

Step 8

After that click on Run button as highlighted in the below given image −

Step 9

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.

Step 10

After the completion of all the steps, C column will hide automatically. Consider below given output snapshot for reference.

Conclusion

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.