#1
|
||||
|
||||
Hide and Unhide Rows If....
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 |
#2
|
|||
|
|||
Quote:
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:
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 |
#3
|
||||
|
||||
Shocks!!! The removal of 26 solved problem 1 and 2 and the macro is doing its job in seconds.
THANK YOU. |
Thread Tools | |
Display Modes | |
|
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 |