Do your financial and operational reports include rows with zero values? Do you feel that your reports would be tidier and more readable if you could hide these rows? Because Sage Intelligence Reporting uses Microsoft® Excel®, you can easily remove these rows. In this tip we will show you how, using Macros.
Add the Developer tab to Excel
Before creating Macros you will need to add the Developer tab to the ribbon in Excel. The Developer tab lets you create and run Macros and edit Visual Basic (VB) code for Excel. Adding the Developer tab is a global option, thus making it available every time you open Excel or run a report.
- With Excel open, click the File
- Click Options on the left to open the Excel Options
- Click Customize Ribbon on the left.
- Select Main Tabs under Customize the Ribbon on the right.
- Check the Developer item in the list.
Add the VB code to your workbook
- Run out the report you would like to add the Macros to. In this example I have run out a Sales Details report.
- To use Macros, a workbook needs to be saved in a Macro enabled format. So save the workbook to a familiar location like your desktop, using the extention .xlsm.
- With the workbook open, press Alt+F11 OR select the Developer tab and then click the Visual Basic button to open the Visual Basic for Applications (VBA) editor.
4. Double click on ThisWorkbook in the object menu on the left to open the object in the editor.
5. Copy and paste the VB code, available in this document, into the editor, starting from Sub HideZeroRows() all the way to End Sub. This code includes two Macros, one to hide zero rows and one to show zero rows.
The editor will now look as follows:
- Click the save icon on the editor menu to save your Macros. You can now close the editor.
Using the Macros
The Macros work by making a selection and then running either one of them. If you make a selection that includes a zero in any column and then run the HideZeroRow Macro, the row containing the zero will be hidden. Likewise, if you make a selection that includes a hidden row containing a zero and run the ShowZeroRow Macro, the hidden row will be shown. To see how this works, try the following.
- Find one or more rows in your report that contains zero values.
2. Make a selection that includes the zero values.
3. On the Developer tab, click Macros. The Macro window will open, listing the available macros including HideZeroRows and ShowZeroRows.
4. Select ThisWorkbook.HideZeroRows from the list and click run. The rows with zero values will be hidden in your report.
5. To show the hidden rows, make a selection that includes the hidden rows and then go back to the Macros window. Select ShowZeroRows and click run. The rows will appear again.
Remember that in order for your Macros to be available the next time you run your report, you will need to link the workbook back to the report in the Report Manager.
