Categories
Excel

How to Hide Rows in Excel Using VBA

Hide rows with VBA

Spreadsheet users don’t always need or want to see detailed revenue numbers in reports. Sometimes a quick peek at the totals is sufficient.

While you can manually hide rows and columns in a Microsoft Excel workbook, a little VBA code can automate the process. Adding a VBA macro to your worksheet lets everyone hide and unhide rows, as needed.

In this article we’ll look at how to hide and unhide rows and columns… and we include VBA code examples so you can try it for yourself.

How to Hide Rows with VBA Code

You can use VBA to hide or unhide rows in an Excel worksheet using the Hidden property of the Range object. In the worksheet below, rows 4 through 7 contain detailed revenue figures while row 8 contains totals.

Sample worksheet summarizing sales by quarter

Here’s how to hide multiple rows with just a few lines of VBA code.

  1. Open the Excel VBA editor by pressing Alt + F11 and insert a module, if necessary.
  2. Add a procedure called Sub HideDetails ().
  3. Add the VBA code Range(“4:7”).EntireRow.Hidden = True

Execute Sub HideDetails () in the Visual Basic editor to hide rows 4 through 7. Note that these are contiguous rows.

To unhide all the rows containing detailed revenue, create a second Sub procedure.

  1. Add a Sub procedure called ShowDetails ().
  2. Type Range(“4:7”).EntireRow.Hidden = False

Here are the Excel VBA code examples to hide and unhide the contiguous rows mentioned above.

VBA code to hide rows

In Sub HideDetails (), we referenced multiple rows using the Range object’s EntireRow property, then set the Hidden property to True to hide rows 4 through 7.

To unhide rows 4 through 7, Sub ShowDetails () sets the Hidden property to False.

How to Hide Columns with VBA Code

If you want to use VBA to hide (or unhide) columns, be sure to reference columns with the Range object then set the Hidden property to True or False, as needed.

Here is the the process.

  1. Open the Excel VBA editor by pressing Alt + F11 and insert a module, if necessary.
  2. Add a Sub procedure called HideDetails ().
  3. Type Range(“B:E”).EntireColumn.Hidden = True

Execute Sub HideDetails () in the Visual Basic editor to hide columns B through E. Remember, you have to specify column letters and we’re going to hide entire columns (including empty columns).

To unhide all the columns containing detailed revenue, create a second Sub procedure.

  1. Add a Sub procedure called ShowDetails ().
  2. Type Range(“B:E”).EntireColumn.Hidden = False

Here are the completed VBA code examples.

VBA code to hide columns

Hide Rows and Columns with Minimal VBA Code

You can use VBA to hide rows and columns in any macro-enabled worksheet by making changes to the Hidden property.

Using VBA to hide rows and columns automates what would otherwise be a tedious task – and that’s exactly when you should use VBA. VBA is an excellent way to put complex or repetitive tasks on autopilot.

Go ahead and open the Visual Basic editor and give it a try!