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.

  1. Sub hideunhid()
  2. Dim hsht As Worksheet
  3. Dim str As String
  4. Dim i As Integer
  5. Set hsht = Sheets("Q94")
  6. str = InputBox("Enter a string")
  7. hsht.Range("R5") = str
  8. For i = 1 To 10
  9. If hsht.Range("R" & CStr(i)) = str Then
  10. hsht.Columns("R").EntireColumn.Hidden = True
  11. GoTo l1
  12. End If
  13. Next i
  14. l1: MsgBox "Please see that column R is hidden"
  15. hsht.Columns("R").EntireColumn.Hidden = Fasle
  16. End Sub

The screenshot of the editor is as shown below.

excel vba hide and unhide rows and columns  in vba

 

You can find similar Excel Questions and Answer hereunder

1) Line break in vba message box in Excel

2) How do I assign a macro to a shape using VBA?

3) How can I export a WorkSheet as a PDF using VBA?

4) Here some explanations about the MSXML who stands for Microsoft XML core services

5) How can worksheet functions be accessed in VBA?

6) How can I export a chart as a gif file?

7) How to do webscrapping in VBA. Here some basics

8) How can I get users to select a folder to save the output of my macro?

9) How do i put double quotes in a string in vba in Excel

10) How can I save a WorkSheet as a new WorkBook using VBA?

 

Here the previous and next chapter