Hide rows using VBA in Excel

Hiding one or more rows on a worksheet

Hiding a row can be simple. Below code hides row C on the active worksheet. If you set the property Hidden to False the row becomes visible again.


Rows(2).Hidden = True

Below code hides multiple rows (2, 3 and 4).


Rows("2:4").Hidden = True

You can hide rows when they are not necessarily for a purpose e.g. only the first are important, or only the totals


Sub HideRowsBasedOnValue()
    If Range("A1").Value = "Top pages" Then
        Range("10:20").EntireRow.Hidden = True
    Else
        Range("10:20").EntireRow.Hidden = False
    End If
End Sub

Hiding rows in a range

error '1004': Unable to set the Hidden property of the Range class

When starting with a range which is not a complete row you will get run-time error '1004': Unable to set the Hidden property of the Range class.


Dim rng As Range: Set rng = Application.Range("B2")
rng.Hidden = True

You can fix this by applying EntireRow to the range:


Dim rng As Range: Set rng = Application.Range("B2")
rng.EntireRow.Hidden = True

Make a row xlVeryHidden

xlVeryHidden is not supported for rows. A workaround is to hide it and then protect the sheet.

Getting the code using Code VBA

The animation below shows how the Code VBA add-in helps with coding this task.

  1. Select Object 'Sheet1' - here we want to hide rows in the same workbook as the one that has the code module
  2. Select Rows - the collection you want to work on
  3. Select Properties and Hidden - the visibility is determined using the 'Hidden' property
  4. Let the property have the value 'True'
  5. Specify the subset of the Rows you want to hide
hide rows