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.
Also Read: How to Hide or Unhide Row or Column in Excel
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.
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.
You can also hide multiple rows using the rows function.
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.
Also Read: Hide / Unhide Gridlines in Excel – VBA
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.
This brings us to an end.
Thank you for reading.