#1
|
||||
|
||||
Hide Row With Zero Values
Hi. I have several sheets in 1 workbook that have Ending balances in columns H and I. I googled first hoping that I would find a solution to my problem but found nothing that met my exact requirements. The samples that I found refer to a single column only, not 2 columns. Every month after supplying the data for my monthly reports, I filter the rows under Col H and I with zero values. I would like a code that, when run will hide the rows in col H and I that have zero values. Thank you. Sample sheet attached. Last edited by Marcia; 03-17-2020 at 02:24 PM. |
#2
|
|||
|
|||
Hi Marcia,
How about this Code:
Sub HideRow2() Dim c As Range Dim ws As Worksheet Dim rng As Range Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets Set rng = ws.Range("J3:J" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row) rng.Formula = "=IF(SUM(H3:I3),"""",""hide"")" For Each c In rng If c.Value = "hide" Then ws.Rows(c.Row).EntireRow.Hidden = True Next c Next ws Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
another possibility
Code:
Sub HideZeroValueRows() Dim ws As Worksheet, rng As Range, cel As Range For Each ws In ThisWorkbook.Sheets Set rng = ws.Range("H3", ws.Cells(Rows.Count, "I").End(xlUp).Offset(-1)) For Each cel In rng If cel.HasFormula And cel.Value = 0 Then cel.EntireRow.Hidden = True Next cel Next ws End Sub |
#4
|
||||
|
||||
Thank you Jeffrey and Sparks for the code. Both work great in the sample file but I failed to mention that the actual workbook contains sheets that should not be affected by the HideRows macro. The macro should be applied to specific sheets in the workbook.
|
#5
|
|||
|
|||
There are numerous ways to do accommodate that, have a look at
Loop Through Worksheets / Sheets with Excel VBA — Excel Dashboards VBA and more |
#6
|
||||
|
||||
Thank you. I inserted a line that I copied from the link for the selection of sheets then I got this error message.
Quote:
|
#7
|
|||
|
|||
Marcia,
You might want to look at the link again that NoSparks posted. You End the case statement therefore the following line does not know what ws is. |
#8
|
||||
|
||||
Right Jeffrey. I moved the End Select before Next ws. Thank you.
Code:
Sub HideZeroValueRows() Dim ws As Worksheet, rng As Range, cel As Range For Each ws In ThisWorkbook.Sheets Select Case ws.Name Case Is = "TB..GF 101", "TB..GF 20%", "TB..SEF", "TB..REG TF" Set rng = ws.Range("EG9", ws.Cells(Rows.Count, "EH").End(xlUp).Offset(-1)) For Each cel In rng If cel.HasFormula And cel.Value = 0 Then cel.EntireRow.Hidden = True Next cel Case Is = "TB - ALL FUNDS", "TB..GF CONSO" Set rng = ws.Range("F9", ws.Cells(Rows.Count, "G").End(xlUp).Offset(-1)) For Each cel In rng If cel.HasFormula And cel.Value = 0 Then cel.EntireRow.Hidden = True Next cel End Select Next ws End Sub |
#9
|
||||
|
||||
Hi Jeffrey, I modified the Code in Post #2 as follows:
HTML Code:
rng.Formula = "=IF(E2<>"""",""Printed"",IF(B2<>"""",""Print"",""""))" For Each c In rng If c.Value = "Printed" Then ws.Rows(c.Row).EntireRow.Hidden = True If c.Value = "" Then ws.Rows(c.Row).EntireRow.Hidden = True Next c Another please. The range is F2:F26 but the formula when run ends until F2626 not F26. Thank you. Last edited by Marcia; 03-19-2020 at 08:07 PM. |
#10
|
|||
|
|||
Can you supply a sample workbook with this setup since it is different from post #1?
Also, if your last cell is F26, but yet the end is showing F2626, then you must have blank rows beyond F26 that aren't really blank. What do you get when you select Ctrl + End? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table with values from several locations and years - need to find comp values | Ricardo Sousa | Excel | 9 | 07-27-2018 02:06 AM |
Table with values from several locations and years - need to find comp values | Ricardo Sousa | Excel | 6 | 06-09-2018 10:51 PM |
Display multiple values as new values based on selection from template. | MvdB | Excel | 2 | 09-29-2015 08:51 PM |
Hide rows in multiple columns based on zero values | Deane | Excel Programming | 19 | 06-23-2015 11:24 PM |
How to Hide/Un-hide a worksheet based on cell on another sheet. | easton11 | Excel Programming | 1 | 06-02-2015 12:07 PM |