Hide Rows or Columns with Excel VBA

With Excel with VBA you can hide an entire row or column based on the value in one cell. Perhaps the one cell is an input cell where you put a particular value.  Excel will hide a group of Rows if the value matches and un-hide the rows if the value does not match.  The code to produce this sort of event is remarkably simple. The following VBA procedure will hide data if F10 is equal to zero.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Rows("20:25").EntireRow.Hidden = [f10] = 0
End Sub

The above says when F10 is Equal to 0 then hide Rows 20 to 25. If F10 equals anything other than zero then unhide Rows 20-25. All this without an If Statement.

To do the same thing with Columns change the code only slightly.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Columns("F:G").EntireColumn.Hidden=[H10]=0
End Sub

The above says that when H10 is Equal to 0 then hide Columns F & G.  If F10 equals anything other than zero then unhide Columns F & G. 

Hide with VBA
Put another way with the file as an example, if the Cell in Green Changes to 0 the cells in Pink will be hidden.

Hide Rows Based on Row Value VBA

If you wanted to hide Rows where a row value was equal to 0 for example I would suggest a different method.  You can hide rows using the Autofilter and not show the drop down arrows.  It has the same look and feel as the above technique.  The code to hide all rows with a value of 0 is as follows.

Sub HideNoVal() 'Excel VBA to hide data quickly.
Range("F10", Range("F" & Rows.Count).End(xlUp)).AutoFilter 1, "Yes", , , 0
End Sub

To undo the above coding the file requires the following code.

Sub UnHide() 'Excel VBA to hide the autofilter, show data.
[F10].AutoFilter
End Sub

The file attached shows some practical implications of the 3 examples above.