Do your financial and operational reports include rows with zero values? Do you feel that your reports would be neater and easier to read if you could hide these rows? In this tip, we will show you how to hide zero values using Macros.
Applies To: Microsoft ® Excel® 2010, 2013 and 2016.
Add the Developer tab to Microsoft® 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.
1. With Excel open, click the File
2. Click Options on the left to open the Excel Options.
3. Click Customize Ribbon on the left.
4. Select Main Tabs under Customize the Ribbon on the right.
5. Check the Developer item in the list.
Add the VB code to your workbook
1. Open the report you would like to add the Macros to.
2. 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 extension .xlsm.
3. 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 This Workbook in the object menu on the left to open the object in the editor.
5. Copy and paste the below VB code 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.
Sub HideZeroRows()
Dim rng As RangeDim cnt As LongDim lmt As IntegerDim opt As Integer
lmt = 10000 ‘set the limit for when warning appearsSet rng = Selection ‘set current selection to a variablecnt = rng.Count
If cnt > lmt Thenopt = MsgBox(“You have made a large selection and will take some time for your Macro to run. Click Yes to continue or No to make a smaller selection.”, vbYesNo, “Warning”)End If
If cnt <= lmt Or (cnt > lmt And opt = vbYes) ThenFor Each cell In rngIf (Not IsEmpty(cell) And cell.Value = 0) Thencell.EntireRow.Hidden = TrueEnd IfNextEnd If
End Sub
Sub ShowZeroRows()
Dim rng As RangeDim cnt As LongDim lmt As IntegerDim opt As Integer
lmt = 10000 ‘set the limit for when warning appearsSet rng = Selection ‘set current selection to a variablecnt = rng.Count
If cnt > lmt Thenopt = MsgBox(“You have made a large selection and will take some time for your Macro to run. Click Yes to continue or No to make a smaller selection.”, vbYesNo, “Warning”)End If
If cnt <= lmt Or (cnt > lmt And opt = vbYes) ThenFor Each cell In rngIf (Not IsEmpty(cell) And cell.Value = 0) Thencell.EntireRow.Hidden = FalseEnd IfNextEnd If
End Sub
The editor will now look as follows:
6. 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.
1. 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.
To make running your Macros easier, you may want to add your own buttons to the ribbon and link them to your Macros. You can see an example of this in the image below. There is a button for each Macro in a custom tab called Sandbox.
By hiding zeros values you are able to display only the relevant information. This will lead to quick decision making as there will be no need to look through the unnecessary lines of data.
The post How to hide zero values using Macros in Microsoft® Excel® appeared first on Sage Intelligence.
Powered by WPeMatico