Home > Excel VBA macros > Hide or show rows and columns

Hide or show rows and columns in Excel with VBA

A frequently asked question is how to hide rows and/or columns in Excel using a VBA macro.

On this page are examples on how to do it. To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module.

You can also download a zip-compressed spreadsheet with the examples. To unzip, right-click and select whatever Windows suggests to unzip the archive.

Basically you can use two different methods. You can do it "automatically" by putting code in the worksheet's code sheet. The WorkSheet_Change procedure is activated every time something is changed in the worksheet.

The other approach is to run an "ordinary" macro that checks if certain conditions are met. Let us start by using the last method.

The first example shows how to hide empty columns, and the next example shows how to use the search function and hide rows, if a certain value exists in a given range.

However before we start playing hide and seek, I want to show you how to make the hidden rows and columns visible again!

Sub ShowHidden()

'Makes all hidden rows and columns in the active sheet visible.

Rows.Hidden = False
Columns.Hidden = False
End Sub

Hide empty columns

This is a pretty simple example on how to hide empty columns in a range - here column 1 to 26 (A to Z).

Sub HideEmptyColumns()

'Loops the first 26 columns and hide them, if they are empty.

Dim rCell As Range
Dim rTest As Range
Dim lCol As Long

On Error GoTo ErrorHandle

'Switch off screen updating for speed.
Application.ScreenUpdating = False

'We now loop through the columns from left to right.
For lCol = 0 To 25 Step 1
   'rCell is set = the cell lCol to the right of cell A1.
   Set rCell = Range("A1").Offset(0, lCol)
   'If the cell is empty
   If Len(rCell.Value) = 0 Then
      'Go to the first cell with content below.
      'If there is none "on the way" we land
      'in the bottom row.
      Set rTest = rCell.End(xlDown)
      If rTest.Row = Rows.Count And Len(rTest.Value) = 0 Then
         'Hide the column.
         Columns(rCell.Column).Hidden = True
      End If
   End If
Next

BeforeExit:
Set rCell = Nothing
Set rTest = Nothing
'Switch screen updating back on.
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure HideEmptyColumns"
Resume BeforeExit
End Sub

Using the search function

The following example uses Excel's search function in a given range - here column B, but that can easily be changed.

We ask the user what to search for, and a row is hidden if it has the seach value in column B. Note that "?" and "*" can be used as wildcards or "jokers".

Sub FindHide()
'Hides all rows with a certain value in column B.

Dim vFind
Dim rSearch As Range

On Error GoTo ErrorHandle

'We ask for a search string. "?" and "*" in the search string
'will work as wildcards/"jokers".
vFind = InputBox("What to search for?")
If Len(vFind) = 0 Then Exit Sub

Application.ScreenUpdating = False

'In this example we search column B only.
'If you want to search the entire worksheet, you write
'e.g. "With ActiveSheet" or "With Worksheets(nb. or name)".
'You can also define another range than column B.
With Columns("B:B")
   Set rSearch = .Find(vFind, LookIn:=xlValues)
   'If found:
   If Not rSearch Is Nothing Then
      'Hide the row.
      rSearch.EntireRow.Hidden = True
      'Now we start a loop that finds all other
      'instances. Instances in hidden row are
      'not found by the search.
      Do
         Set rSearch = .FindNext(rSearch)
         If Not rSearch Is Nothing Then
            'If the search result isn't
            'nothing, we hide the row.
            rSearch.EntireRow.Hidden = True
         Else
            'If rSearch was indeed = Nothing, there was
            'no instances and we exit the loop.
            Exit Do
         End If
      Loop
   End If
End With

BeforeExit:
Set rSearch = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure FindHide"
Resume BeforeExit
End Sub

Automatic code

The next two macros are examples of what I call "automatic code," because they execute automatically, if a certain condition is met. So you don't need to do anything active like clicking a button or the like to run the code.

You can do that by putting VBA code into the worksheet's own code sheet. Then events can execute a macro automatically.

We use the standard procedure "Worksheet_Change", which is called automatically when something changes in the worksheet. If you cannot see the worksheets code sheet in the VBA editor press CTRL+R to open Project Explorer:

Project Explorer

The following Worksheet_Change procedures must be in the worksheet's code sheet to work.

The first example is very simple: If you write an "x" in column A, the row with the "x" will be hidden. If you write a 0 (zero) all hidden rows will be visible again, and old values in column A are deleted.

In the last example you can type something in cell A1, and all rows (except row 1) that have the same value in column A will be hidden.

It is not unlike the example, where we used the search function, but here we loop through the cells instead.

Hide rows with an x

Private Sub Worksheet_Change(ByVal Target As Range)

'This macro shows how to hide rows automatically by
'typing a certain or random letter in column A. It
'doesn't have to be column A - it could be any column.
'In the example all hidden rows are made visible,
'if cell A1 gets a value of 0 (zero).

On Error Resume Next

'If the change wasn't in column A, we exit.
With Target
   If .Column <> 1 Then
      Exit Sub
   Else
      'Omit the following if it doesn't matter
      'what is typed. In that case you must check
      'if something was typed, because otherwise
      'the row will be hidden, if the user
      'changed the cell format in some way without
      'writing anything. You can check if the cell
      'is empty by writing an If-then with
      'Len(Target.Value) > 0
      If .Value = "x" Then
         'Hide the row
         .EntireRow.Hidden = True
      End If
      'If it is a zero all hidden rows are made
      'visible, and values are deleted.
      'We check if the length is > 0  -
      'otherwise we could be fooled by e.g. the Delete key.
      If .Value = 0 And Len(.Value) > 0 Then
         'To avoid repeated calls to this
         'procedure, use:
         Application.EnableEvents = False
         'Show all hidden rows
         Rows.Hidden = False
         'Delete all old values
         Columns(1).Clear
         Application.EnableEvents = True
      End If
   End If
End With
End Sub

Hide rows using criteria

The last example shows how to hide all rows that have the same value in column A as in cell A1.

For that matter the whole thing could be written in the worksheet's Worksheet_Change procedure, but for the sake of the example I have made the Worksheet_Change procedure call an "ordinary" macro to do some of the work.

So you must put the Worksheet_Change procedure in the worksheet's code sheet and the procedure HideUsingCriteria in a standard module.

Private Sub Worksheet_Change(ByVal Target As Range)

'This procedure is called automatically when
'something changes in the worksheet.

On Error GoTo ErrorHandle

'If it isn't cell A1, we exit.
If Target <> Range("A1") Then
   Exit Sub
Else
   'Switch off screen updating
   Application.ScreenUpdating = False
   'If it is a zero or the value has been
   'deleted, all rows are made visible.
   If Target.Value = 0 Then
      'Show hidden rows.
      ActiveSheet.Rows.Hidden = False
   ElseIf Len(Target.Value) > 0 Then
      'The following command prevents repeated calls
      'to this procedure, when the macro makes changes.
      'When finished, we switch event handling back on.
      Application.EnableEvents = False
      'Calls the procedure HideUsingCriteria
      '(in Module1) and passes the value in
      'cell A1. Of course it could find that
      'by itself, but this is just an example :-)
      HideUsingCriteria Target.Value
      'Switch event handling back on.
      Application.EnableEvents = True
   End If
End If

BeforeExit:
Application.ScreenUpdating = True
Range("A1").Activate
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure Worksheet_Change"
Resume BeforeExit
End Sub

Insert the following procedure in a standard VBA module.

Sub HideUsingCriteria(ByVal sXY As String)

'This procedure is called by the Worksheet_Change procedure
'in the worksheet's code sheet. It hides all rows (except row 1)
'that have the same value in column A as cell A1.

Dim rCell As Range
Dim rRange As Range

On Error GoTo ErrorHandle

Set rCell = Range("A" & Rows.Count)
If rCell.Value = sXY Then
   rCell.EntireRow.Hidden = True
End If

'We now find the cell (with content) closest to the bottom.
Set rCell = rCell.End(xlUp)

'If it is cell A1 we exit.
If rCell.Row = 1 Then GoTo BeforeExit

'Else we set the range for looping to A2 to the bottom
'cell with content.
Set rRange = Range(rCell, Range("A2"))

'We loop through the range, and cell values are compared to
'the value in A1. If they are identical, we hide the row.
For Each rCell In rRange
   If rCell.Value = sXY Then
      'Hide row
      rCell.EntireRow.Hidden = True
   End If
Next

BeforeExit:
Set rCell = Nothing
Set rRange = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure HideUsingCriteria"
Resume BeforeExit
End Sub

That was it. As mentioned you can download a zipped spreadsheet with the examples.

Related: