Hide / Unhide Rows and Columns using VBA

This blog will teach us to hide columns or rows using VBA. Hiding rows and columns declutters a spreadsheet and improves its readability.

Hidden Property in VBA

The Hidden property of VBA can be used with any Range Object to hide the range of cells. This property is both read and write which means that we can change its value through VBA code. The Hidden property can be set to either True or False so as to hide the columns or rows.

The important thing is that we can use this property only with a complete row or column and not just a range. This will hide columns or rows using VBA.

We can use the EntireRow or EntireColumn property to refer to the row or column of the range.

For instance, to hide the columns containing the range A1:C4, we can use the following code.

Range("A1:C4").EntireColumn.Hidden = True

This hides Columns A, B, and C of the active worksheet.

EntireRow and EntireColumn property

The EntireRow property can be used with a Range Object and it returns the corresponding rows contained in the Range Object. Similarly, the EntireColumn property returns the columns that are contained in the Range Object. We use the EntireRow or EntireColumn property to get the reference to the complete row or column. This is so because the Hidden property can be used only with the rows and columns and we cannot directly use the Hidden property with the Range Object.

For instance, this is invalid.

Range("A:A").Hidden=True 'This is invalid

We use the EntireColumn property.

Range("A:A").EntireColumn.Hidden=True  'This is valid

You can also use the Columns or Rows function to get the reference to a column or row as follows.

Columns("A:C").Hidden=True
Rows("1:5").Hidden=True

Hiding Multiple Rows or Columns

We can use the Columns property to hide multiple columns. For instance, you can hide the column from A:E using the following code.

Columns("A:E").Hidden=True

You can also hide multiple rows using the rows function.

Rows("1:5").Hidden=True

To hide the columns contained in a specific range, you can use the EntireColumn property. For instance, to hide the columns of range A1:C4 you can use the following Sub procedure.

Sub hidecolumns()
Range("A1:C4").EntireColumn.Hidden = True
End Sub

You can see that columns A, B, and C are hidden after you run the Sub procedure.

You can also hide the rows contained in this range with the following code.

Sub hiderows()
Range("A1:C4").EntireRow.Hidden = True
End Sub

Hiding Non-Contiguous Columns

We can hide the columns that are noncontiguous or adjacent to each other. For this, we can use the Range object where the two sets of columns are separated with commas.

For instance, you can hide the columns A, C, and E as follows.

Sub hideNonContiguousColumns()
Range("A:A,C:C,E:E").EntireColumn.Hidden = True
End Sub

Hide or Unhide All Rows

We can hide or unhide all the rows of the active worksheet with the following code. The Rows property of the worksheet refers to all the rows and we can set the hidden property to toggle its state. We are using the Not function to turn off / on the hidden property depending on the current value.

Sub hideUmhideAllRows()
ActiveSheet.Rows.Hidden= _
Not(ActiveSheet.Rows.Hidden)
End Sub

Unhide Rows or Columns

You can set the value of the Hidden property to False so as to unhide the rows or columns.

Columns("A:E").Hidden=False
Rows("1:1").Hidden=False

This brings us to an end.

Thank you for reading.