Hiding columns in VBA
By using Worksheet’s Columns property, you may hide one or more columns in VBA.
This is how you may use the Columns property:
OR
- You may hide one or more columns by using this property
- If Columns is used as above, it will affect the active worksheet’s column(s)
- By using Worksheet. Columns, you may specify another worksheet that is not active.
An example of hiding one column in an active sheet
Our sample sheet contains four columns as shown in the image below:
VBA code to hide Column “C”:
Sub hide_cols()
'Hiding col C
Columns("C").Hidden = True
End Sub
Result:
Hiding two columns example
To hide two or more columns, enclose this in the double quotes separated by a colon.
The example below hides the B and C columns in our Excel sheet:
Code:
Sub hide_cols()
'Hiding col C and D
Columns("B:C").Hidden = True
End Sub
Result:
Similarly, you may hide 3 or more columns in the active sheet. For example, this will hide B, C, and D:
How to hide columns in another sheet?
So, if you need to hide the column(s) in another Worksheet then use the “Worksheets” object.
To show this demo, we have two sheets in our Workbook:
- Sales (active)
- Product Information
The product information sheet contains four columns (as shown in the first example).
We need to remove a column in that sheet.
VBA code:
Sub hide_cols()
'Hiding col in another sheet than active
Worksheets("Product Information").Columns("C").Hidden = True
End Sub
Similarly, you may hide two or more sheets by using syntax i.e.
Unhide the hidden columns
To unhide the hidden column(s), just set the Hidden = False.
