MS Excel — Hide Rows Based on Cell Value (Automation Tip)
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 — 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!