locked
Excel macro to hide dynamic blank rows and static rows RRS feed

  • Question

  • I have a template/worksheet A1:E201 that is used as a packing list with rows 1-7 as a header, and rows 8, 44, 100, 151 as sub headers. I need a macro that will hide the sub header rows and any blank rows. I get approximately 10-20 of these files daily and doing the CTRL-click each row is so time consuming. I have found several methods for each type, dynamic and static but none that cover both. Can anyone help me out? I am using Office 2003 Pro.
    Monday, September 24, 2018 6:17 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.

    Tuesday, September 25, 2018 2:54 AM
  • Hi Ptbo-Pete,

    >>hide the sub header rows and any blank rows.

    Please use the code below:

    Sub Change()
        Dim xRg As Range
        Application.ScreenUpdating = False
            For Each xRg In Range("A1:E201")
                If xRg.Value = "" Then
                    xRg.EntireRow.Hidden = True
                Else
                    xRg.EntireRow.Hidden = False
                End If
            Next xRg
            Range("A8").EntireRow.Hidden = True
            Range("A44").EntireRow.Hidden = True
            Range("A100").EntireRow.Hidden = True
            Range("A151").EntireRow.Hidden = True
        Application.ScreenUpdating = True
    End Sub

    Hopefully it helps you.

    Best Regards,

    Lina


    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.


    • Edited by Lina-MSFT Tuesday, September 25, 2018 6:50 AM
    Tuesday, September 25, 2018 6:44 AM
  • Thanks Lina, however the blank line portion does not work as all lines, except #4, are hidden. The sub-header portion works but not the blank row part. The data is populated from another page if that makes any difference although rows 1 to 8 do have static and dynamic data so I have no idea why it hides rows 1-8 with the exception of 4. Would it help if I sent an example file?
    Tuesday, September 25, 2018 6:51 PM
  • Lina, I have the solution(s) as follows, thanks for your guidance:

    Sub Hide_Minor()
        Sheets("Packing List").Select
         ActiveSheet.Unprotect
         Dim r As Range, c As Range
     Set r = Range("a9:e201")
     Application.ScreenUpdating = False
     For Each c In r.Rows
         If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
             c.EntireRow.Hidden = True
         Else
             c.EntireRow.Hidden = False
         End If
     Next c
            Range("A8").EntireRow.Hidden = True
            Range("A44").EntireRow.Hidden = True
            Range("A100").EntireRow.Hidden = True
            Range("A151").EntireRow.Hidden = True
    '    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     Application.ScreenUpdating = True
     End Sub

    Sub Hide_Major()
        Sheets("Packing List").Select
        ActiveSheet.Unprotect
         Dim r As Range, c As Range
     Set r = Range("a9:e201")
     Application.ScreenUpdating = False
     For Each c In r.Rows
         If Len(c.Cells(1).Text) + Len(c.Cells(2).Text) = 0 Then
             c.EntireRow.Hidden = True
         Else
             c.EntireRow.Hidden = False
         End If
     Next c
            Range("A1:A8").EntireRow.Hidden = True
            Range("A44").EntireRow.Hidden = True
            Range("A100").EntireRow.Hidden = True
            Range("A151").EntireRow.Hidden = True
    '    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
     Application.ScreenUpdating = True
     End Sub

    • Proposed as answer by Lina-MSFT Wednesday, September 26, 2018 1:16 AM
    Tuesday, September 25, 2018 8:57 PM
  • Hi Ptbo-Pete,

    please mark the solution as answer, it will make others who stuck with the similar issue easier to search for valid solutions in this forum.

    I wish you a happy life!

    Best Regards,

    Lina


    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.


    • Edited by Lina-MSFT Wednesday, September 26, 2018 1:19 AM
    Wednesday, September 26, 2018 1:18 AM