Sage 100

Sage Intelligence - Hiding Zero Rows Anyone use Excel Conditional Formatting to hide zero rows or only the macro that SI published?

Never mind, doesn't hide the row but neat idea to use this for formatting tasks in SI.

I wrote a macro for it if you still need it.

I'd love it, thank you. It's different from this one, right? https://www.sageintelligence.com/tips-and-tricks/sage-intelligence-tips-tricks/2015/05/hide-zero-rows-using-excel-functionality/

@LouWagner, does it allow for changing the period/year on the fly? Or do you just run the macro again after changing period/year. We have been using Number Filters recently on the column but user has to 'reapply' after changing period/year.

I use the following formula in a column and then do a filter on that column to exclude the 0s. =IF(AND(MIN(F14:H14)=0, MAX(F14:H14)=0),0,1) I always tell users to reapply the filter when generating the report.

@MoiraGoggin mine is not the same, however, I posted it in a thread a while back on there along with an explanation. @NancyHanson I actually run two macros, one to Unhide everything and which I run automatically when the spreadsheet is run out of SI. Then the Hide macro is a button on the first sheet in the workbook that the user can click on. @MaryJoKrueger I like that. I'm going to try to see if that's something that I can use here instead. Bear with me while I find my macro and post it up.

Macro text: Sub UnhideRows() Dim sh As Worksheet Application.Calculation = xlCalculationManual Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets sh.Cells.EntireRow.Hidden = False Next sh Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Sub HideZeroRows() Dim sh As Worksheet Dim i As Long On Error Resume Next Application.Calculation = xlCalculationManual Application.ScreenUpdating = False For Each sh In ThisWorkbook.Worksheets For i = 8 To 348 If Not IsEmpty(Range(""w"" & i)) Then If IsNumeric(Range(""w"" & i)) Then sh.Rows(i).Hidden = Application.Sum(sh.Range(""w"" & i).Resize(, 36)) = 0 End If End If Next i Next sh Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

Thank you @LouWagner !!! Do you have this as a macro assigned in Report Designer or in the template itself?

Notes: I write macros for me, not usually for others so if this makes a spreadsheet messy, it's probably inconsequential. For instance, this macro hides all zero rows in all sheets, including the hidden sheets. At least, it used to. I never unhide those sheets so perhaps I've evolved the macro enough to prevent this non-problem now. You need a helper column. In the above example, it's column w. In that column, total the entire row of values and then, any row with a zero total will get hidden. If you don't want to hide a particular line, as with headings, don't put a total in that row. You need to say how tall your sheets are. I assume that when I'm creating sheets, they're always the same number of rows so in this case, set the for i = 1 to xxx to the row count of your tallest sheet. In this case, 348 rows. Set up buttons on the first sheet for Unhide and Hide and then you can just click the button to run the macro. Easier that way and I even have some users I've trained to do that for themselves. @MoiraGoggin In the Report Designer, run the Unhide macro. Always. :-)

Of course, if you need anything else from me to clarify, let me know!