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.

* Tip — Never use a pivot table as a report for distribution. Create a dynamic report and use pivot tables as your source of data.

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).

• 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.

• Open the Macro Editor (ALT + F11) and insert the VBA code mentioned above. Then, create a button and assign the macro to that button.

• When the underlying data updates, the report date is changed. This dynamically updates all column headings.

• Click the “Refresh Report View” button and the report updates for the current months data. ✅ Done!

• Here is the final report view in printed form. No buttons appear, etc. A final clean format.

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

“It’s time for DIFFERENT”

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