MS Excel — Hide Rows Based on Cell Value (Automation Tip)

And Why It’s Helpful!

I’m working on a reporting project and had a need to solve this issue…

Here’s a sample scenario:

  • Creating a dynamic trend report that pulls the data from a pivot table*.
  • The number of lines in the report can vary each month (so, imagine a list of 50 products one month and 60 the next month as products are added).
  • So, each month when the report is updated, I want to click a button and refresh the view for the correct number of lines.

Here’s the answer, which I tweeted earlier:

Now, let’s walk through how this looks on a sample report.

Report Format

  • Report format — extra rows are provided for since source data will fluctuate (e.g., increase each month).
Image for post
Image for post
  • Create a “check” column (which gets hidden in final spreadsheet), which counts the characters in Column A of the report. The LEN function is used to do this.
Image for post
Image for post
  • Open the Macro Editor (ALT + F11) and insert the VBA code mentioned above. Then, create a button and assign the macro to that button.
Image for post
Image for post
  • When the underlying data updates, the report date is changed. This dynamically updates all column headings.
Image for post
Image for post
  • Click the “Refresh Report View” button and the report updates for the current months data. ✅ Done!
Image for post
Image for post
  • Here is the final report view in printed form. No buttons appear, etc. A final clean format.
Image for post
Image for post

The opportunities to apply this in your reporting processes is dramatic. Good luck!

Let me know in the comments how you can apply this tip!

About Don

Image for post
Image for post

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store