I need to hide rows on a Quote Form I'm building. Can you help me, please? RRS feed

  • Question

  • MS won't let me upload a .jpg snip until my account is verified (yep, I'm a nube). I'm hoping the time I spent putting this question together is not wasted.

    I'm working on a QuoteForm! tab that lists data populated from various cells when values are entered in the SaleSheet! tab. I want to hide Rows 56:88 on the QuoteForm! tab whenever they have values less than 1 in Column A as well as the associated Product Heading Row (typically the Bold Text row above the values returned from the SaleSheet! tab).

    I found a block of VB code here in the community (thanks Ron Rosenfeld) that seems to do most of what I need, but I can't get the Header Row above the block of data to hide. In this example, I need to hide Rows 61:67 when the value in Column A is less than 1. Of course, this would need to repeat for each Product Heading on the QuoteForm! tab.

    I am anchoring each Product Heading Row with the number .999 (hidden) in Column A to give the code something to look for. You can see Rows 62 and 65 are hidden because they have values of 1 or greater where A62 =IF(SaleSheet!B27<>0,SaleSheet!B27,"") and Cell A62 is formatted as a Number with no decimal points. Here is the code I am using.

    Option Explicit
    Private Sub Worksheet_Activate()
        Dim r As Range, c As Range
    Set r = Range("a56:a88")
    Application.ScreenUpdating = False
    For Each c In r
        If Len(c.Text) <= 0.999 Then
            c.EntireRow.Hidden = True
            c.EntireRow.Hidden = False
        End If
    Next c
    Application.ScreenUpdating = True
    End Sub

    Friday, May 1, 2020 1:45 AM

All replies

  • Hi,

    Since this forum focuses more on questions related to Office desktop applications, if you need further assistance on this problem, I'd like to help you move the thread to the following forum: Excel for Developers Forum. You may get more helpful replies there.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thanks for your kind understanding.

    Best Regards,


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Friday, May 1, 2020 8:28 AM