How to hide Excel Sheet Columns in VBA

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:

Columns("C").Hidden = True

OR

Columns(3).Hidden = True
  • 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-Hide-Column-sample

VBA code to hide Column “C”:

Sub hide_cols()

'Hiding col C

Columns("C").Hidden = True

End Sub

Result:

VBA-Hide-Column-single

Hiding two columns example

For hiding two or more columns, enclose this in the double quotes separated by a colon.

The example below hides 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:

VBA-Hide-Column-multi

Similarly, you may hide 3 or more columns in the active sheet. For example, this will hide B, C, and D:

Columns("B:D").Hidden = True

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

column-Hide-sheet-othe

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.

Worksheets("Product Information").Columns("B:D").Hidden = True

Unhide the hidden columns

To unhide the hidden column(s), just set the Hidden = False.

Worksheets("Product Information").Columns("C").Hidden = False