Microsoft Office Forums - View Single Post

Marcia · 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.