Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-17-2020, 12:10 AM
Marcia's Avatar
Marcia Marcia is offline Hide Row With Zero Values Windows 7 32bit Hide Row With Zero Values Office 2013
Expert
Hide Row With Zero Values
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Attached Files
File Type: xlsx Hide Rows with Zero Values.xlsx (20.5 KB, 15 views)

Last edited by Marcia; 03-17-2020 at 02:24 PM.
Reply With Quote
  #2  
Old 03-17-2020, 05:24 AM
jeffreybrown jeffreybrown is offline Hide Row With Zero Values Windows 10 Hide Row With Zero Values Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #3  
Old 03-17-2020, 06:07 AM
NoSparks NoSparks is offline Hide Row With Zero Values Windows 10 Hide Row With Zero Values Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #4  
Old 03-17-2020, 10:14 PM
Marcia's Avatar
Marcia Marcia is offline Hide Row With Zero Values Windows 7 32bit Hide Row With Zero Values Office 2013
Expert
Hide Row With Zero Values
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #5  
Old 03-18-2020, 06:00 AM
NoSparks NoSparks is offline Hide Row With Zero Values Windows 10 Hide Row With Zero Values Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

There are numerous ways to do accommodate that, have a look at
Loop Through Worksheets / Sheets with Excel VBA — Excel Dashboards VBA and more
Reply With Quote
  #6  
Old 03-18-2020, 07:08 AM
Marcia's Avatar
Marcia Marcia is offline Hide Row With Zero Values Windows 7 32bit Hide Row With Zero Values Office 2013
Expert
Hide Row With Zero Values
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you. I inserted a line that I copied from the link for the selection of sheets then I got this error message.
Quote:
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"
End Select
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
Next ws
End Sub
Reply With Quote
  #7  
Old 03-18-2020, 07:13 AM
jeffreybrown jeffreybrown is offline Hide Row With Zero Values Windows 10 Hide Row With Zero Values Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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.
Reply With Quote
  #8  
Old 03-18-2020, 08:01 AM
Marcia's Avatar
Marcia Marcia is offline Hide Row With Zero Values Windows 7 32bit Hide Row With Zero Values Office 2013
Expert
Hide Row With Zero Values
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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
We are on a mandatory Work-at-Home due to the COVID-19. We live in the mountains and we thought that we are safe but a seaman in a neighboring town came home positive...
Reply With Quote
  #9  
Old 03-19-2020, 07:11 AM
Marcia's Avatar
Marcia Marcia is offline Hide Row With Zero Values Windows 7 32bit Hide Row With Zero Values Office 2013
Expert
Hide Row With Zero Values
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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
I would like the Code to hide rows if the value in the range is either "Printed" or blank.
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.
Reply With Quote
  #10  
Old 03-20-2020, 08:47 AM
jeffreybrown jeffreybrown is offline Hide Row With Zero Values Windows 10 Hide Row With Zero Values Office 2016
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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?
Reply With Quote
Reply

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
Hide Row With Zero Values 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft