Over the next few minutes, I will show you two techniques for automatically hiding and showing rows in Excel using VBA code. These techniques I use regularly in my real estate financial models to make for a more intuitive user experience.
In the past, I’ve created similar tutorials for hiding worksheets or creating dynamic buttons and toggles. And in fact, this logic can be used in tandem with those techniques to really improve the usability of an Excel model.
To teach this concept, I’ve recorded a short 10 minute video, attached a completed file, and included a link to the VBA I use. I’ve also written out the basic steps for you to follow.
These simple techniques help make complex models easier to navigate and use.
Download the VBA Code and Completed Workbook
To get you started, below find links to download the VBA code and completed file used in the video tutorial.
- Click here to download the basic VBA code used in this tutorial
- Click here to download the basic Excel file created in this tutorial
Video Tutorial – Using VBA to Hide Rows in Real Estate Excel Models
The following 10 minute video will walk you through the process of using VBA to hide and show rows in your real estate Excel models. My recommendation is to open an open Excel workbook and follow along as I teach the techniques. Refer to the written instructions below and be sure to have downloaded the VBA code and completed file above to help you.
Technique #1 – Using a Drop-Down Menu to Hide or Show Rows
The first technique in the video involves using drop-down menus to hide or show rows. This is a really simple and intuitive method, but it’s not without its drawbacks.
The downside to this technique is that the code runs each time a change is made to the worksheet where the code is included. This can dramatically slow down a Workbook. Here are the basic steps for using technique #1.
- Be sure the file is saved as a ‘Macro-enabled Workbook’
- Create a drop-down menu
- Right-click on the tab for the worksheet containing the drop-down menu and select ‘View Code’
- This will open code specific to the selected worksheet
- Enter VBA (download code above) logic that shows or hides defined rows based on the value in the drop-down menu cell
Technique #2 – Using a Buttons to Hide or Show Rows
The second technique in the video involves using buttons to hide or show rows. This is my preferred technique, as the code only runs when a button is pressed. It however takes an additional step or two to build and does not integrate as efficiently into the Worksheet as in-cell drop-down menus do.
- Be sure the file is saved as a ‘Macro-enabled Workbook’
- Insert two shapes and enter ‘Hide’ in one shape and ‘Show’ in the other
- Go to the Developer ribbon and select ‘Visual Basic’ (Don’t see the Developer ribbon?)
- A Visual Basic window will appear, go to Insert>Modules
- A new module with be created with a blank
- Create two Macros (download code above), one to hide a row and the second to show a row
- Close the VBA window and right-click on the shape entitled ‘Hide’; select ‘Assign Macro’ and choose the ‘Hide’ Macro
- Right-click on the shape entitled ‘Show’; select ‘Assign Macro’ and choose the ‘Show’ Macro
