Vba hide and unhide rows and columns in vba in Excel
When dealing with a data-heavy spreadsheet, sometimes it�s helpful to hide or unhide rows and columns to better see the information needed.
This feature is necessary when a large spreadsheet has to effectively convey the message to the end user.
Not all the records are needed for the end user, in that case, it is better to hide the unwanted columns or rows.
Excel makes it easy to do this by allowing the user to hide the unwanted columns.
The following code demonstrates to hide and unhide columns.
The code checks for values in particular column and if found a specific value, then the entire column is hidden.
For illustration purpose, the column R is chosen in which we will purposely put some value in cell R5 and hide the column based on that value.
- Sub hideunhid()
- Dim hsht As Worksheet
- Dim str As String
- Dim i As Integer
- Set hsht = Sheets("Q94")
- str = InputBox("Enter a string")
- hsht.Range("R5") = str
- For i = 1 To 10
- If hsht.Range("R" & CStr(i)) = str Then
- hsht.Columns("R").EntireColumn.Hidden = True
- GoTo l1
- End If
- Next i
- l1: MsgBox "Please see that column R is hidden"
- hsht.Columns("R").EntireColumn.Hidden = Fasle
- End Sub
The screenshot of the editor is as shown below.