Hide and Unhide Rows If....

Marcia · #1 03-20-2020, 05:59 PM

I started a new thread due to a change in settings. From Jeffrey's Code in a previous and related thread, I modified lines 7 to 9. Before Row 21 in the attached sheet, rows will be inserted as daily transactions occur.
1. How do I make the formula accommodate the expanding range, and
2. Why does the formula extend to row 2626?
As if by staring at the code for hours and hours I would solve my problem but it was my (elderly) brain that went to shutdown mode.

HTML Code:

Sub HideRow()
    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("F2:F26" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row)
        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
    Next ws
    Application.ScreenUpdating = True
End Sub
Sub UnHideRow()
    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("F2:F26" & ws.Range("B" & ws.Rows.Count).End(xlUp).Row)
        rng.Formula = "=IF(E2<>"""",""Printed"",IF(B2<>"""",""Print"",""""))"
        For Each c In rng
            If c.Value = "Printed" Then ws.Rows(c.Row).EntireRow.Hidden = False
            If c.Value = "" Then ws.Rows(c.Row).EntireRow.Hidden = False
        Next c
    Next ws
    Application.ScreenUpdating = True
End Sub

Thank you.

NoSparks · #2 03-20-2020, 06:59 PM

Quote:

1. How do I make the formula accommodate the expanding range

If you use real Excel tables that is automatic.
I guess the issue here would be how you are inserting your new data.
Here's a couple of links to sites with info on tables that is essential for vba.
Excel: Working with Tables (VBA)
The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru

Quote:

2. Why does the formula extend to row 2626?

Eliminate the 26 you have inside the quotes. As you have it now
ws.Range("B" & ws.Rows.Count).End(xlUp).Row gives a result of 26
which is added to the end of what is between the quotes giving you F2:F2626

Marcia · #3 03-20-2020, 07:25 PM

Shocks!!! The removal of 26 solved problem 1 and 2 and the macro is doing its job in seconds.
THANK YOU.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to Hide/Unhide empty rows with formula based on criteria in G3?	LearnerExcel	Excel Programming	1	02-15-2018 07:17 AM
How to hide and unhide sheet according to drop down list	aster	Excel Programming	1	04-18-2017 07:47 AM
Hide / unhide Rows	NullVBA	Excel Programming	4	12-09-2015 05:10 AM
Hide/Unhide pictures	maloneb	PowerPoint	1	01-18-2012 03:34 PM
Unhide and Re-hide in Excel 2003	thorgal67	Excel	1	07-16-2009 07:06 AM