none
Excel 2013: how do i hide rows and then prepopulate the following rows RRS feed

  • Question

  • I know nothing about Excel and have been stumbling around trying to solve this requirement with amateurish If, and VLookup statements and gave given up.

    I have a worksheet where A1 is the date, B1 is a drop down list: select from List, Review content, New.

    A2-H2 is the table headings and A3-H22 for example is the table with prefilled info in A3-C22 and then D3-H2 to be completed by the tradesperson doing the service check.

    Once the table has been completed and saved, I want to:

    Hide the completed A3-H22 (either automatically when the sheet is reopened or alternately, via selecting New from the B1 Dropdown list).

    I have managed to do this via:

    Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Range("f2")) Is Nothing Then Exit Sub

        If Target = "Review" Then

            Rows("8:50").Hidden = False

        ElseIf Target = "New" Then

            Rows("8:50").Hidden = True

        End If

    End Sub

    If by doing this the rows are hidden, I then want the succeeding 50 or so rows to Automatically populate with the prefilled a-c content (a3-c50) for example.  And so on and so on, so that when opened the sheet presents the most recent completed 50 cells, but can then hide these and regenerate as previously described.

    The reason for this is so that the person doing the (daily, weekly or monthly) service has a clean electronic form to work with and subsequently the historical data is collected and can then be analysed and whatever other benefits it presents. 

    Thanks in advance for any assistance,

    Wednesday, August 8, 2018 11:33 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    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. Thank you for your understanding.

    Regards,

    Emi


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


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

    Thursday, August 9, 2018 2:48 AM
  • Hello seepaq,

    >> I then want the succeeding 50 or so rows to Automatically populate with the prefilled a-c content (a3-c50) for example.

    What do you mean automatically populate with the prefilled a-c content?

    Besides,I'm confused about your your example code now.  Your said you want to select drop down list in B2 and related range seems A3:H22, so your code is triggered by changing value in F2 and the related range is row 8-50?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 9, 2018 9:43 AM