Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-20-2020, 05:59 PM
Marcia's Avatar
Marcia Marcia is offline Hide and Unhide Rows If.... Windows 7 32bit Hide and Unhide Rows If.... Office 2013
Expert
Hide and Unhide Rows If....
 
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 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
Thank you.
Attached Files
File Type: xlsm Hide Rows Advice.xlsm (18.5 KB, 5 views)
Reply With Quote
  #2  
Old 03-20-2020, 06:59 PM
NoSparks NoSparks is offline Hide and Unhide Rows If.... Windows 10 Hide and Unhide Rows If.... 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

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
Reply With Quote
  #3  
Old 03-20-2020, 07:25 PM
Marcia's Avatar
Marcia Marcia is offline Hide and Unhide Rows If.... Windows 7 32bit Hide and Unhide Rows If.... Office 2013
Expert
Hide and Unhide Rows If....
 
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

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

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
Hide and Unhide Rows If.... How to hide and unhide sheet according to drop down list aster Excel Programming 1 04-18-2017 07:47 AM
Hide and Unhide Rows If.... 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
Hide and Unhide Rows If.... Unhide and Re-hide in Excel 2003 thorgal67 Excel 1 07-16-2009 07:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:11 PM.


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