Hiding Columns Which Meet a Condition


I once had a request to create an Excel VBA procedure which would hide columns if the value in a specific cell was less than another value. If the data was stored in a single column then a filter would be one of the fastest ways to hide the data. Since the data is in columns we can test each item and hide the column if it falls outside of the specified criteria.

The following is an example of how this can be done across multiple columns. The range of cells being evaluated is in Row 11.



Option Explicit
Sub HideIt() 'Excel VBA to hide columns based on criteria.
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating= False
j=[A11]
For i=2 To Range("IV10").End(xlToLeft).Column
If Cells(11, i) < j Then
Cells(11, i).EntireColumn.Hidden= True
End If
Next i
Application.ScreenUpdating= True
End Sub

In Excel there are many ways to do the same thing. the following will perform the same VBA procedure as the above. However it does it with a little less fuss.


Option Explicit
Sub Hideit2() 'Excel VBA to hide columns based on criteria part 2.
Dim rng As Range
For Each rng In Range("B11", Range("IV11").End(xlToLeft))
rng.EntireColumn.Hidden=(rng < [a11])
Next rng
End Sub


To undo the above you may wish to unhide the Columns as well. The following will unhide the hidden columns.


Sub Unhide() 'Excel VBA to unhide a whole range of columns.
Range("B11", Range("IV11").End(xlToLeft)).EntireColumn.Hidden=False
End Sub


Simply change the row from 11 to the row most relevant to your dataset.


Toggle Between Hidden and Unhidden


The following Excel VBA coding was shown to me by a friend on Ozgrid - so thank you Alana. In order to toggle between the columns being hidden and unhidden the following efficient coding will perform this action without using Else as part of the If statement. The Row being evaluated is A1:Z1 which contains the number 1. All of these cells columns will be hidden.

Sub HideIt3() 'Excel VBA to hide columns based on criteria Part 3.
Dim rng As Range

For Each rng In Range("A1:Z1")
If rng=1 Then
rng.EntireColumn.Hidden= Not (rng.EntireColumn.Hidden)
End If
Next
End Sub


The following Excel file shows all three VBA procedures.