Code to hide multiple instances of set rows

[dpaton05]

I have a spreadsheet that calculates costs over a period of time. A button can be pressed that copies the layout below it for data entry of a separate period. I have some code that works correctly to hide rows that have either a 0 or nothing in certain cells for the first period. The problem is that the button can be pressed x number of times but the hide rows code only hides the rows from the first period, it will not hide any rows for additional periods. Could someone please tell me how to change it so it will hide the rows for every period that is in the sheet.

I thought you might need the add rows code as well.

Sub AddRows()
Dim WS As Worksheet, lr As Long, x As Long, AfterCopy As Long, lRow As Long
Set WS = ThisWorkbook.Worksheets("ACA_Quoting")
    Call Move_Shape
    Call InsertExtras
        lRow = Cells(Rows.Count, "H").End(xlUp).Row                                             'Defines lRow as last used cell in column H
            If lRow = 32 Then
                x = 15
            Else
                x = 14                                                                          'The difference in values for x takes into account the extra 2 rows on the first page
            End If                                                                              '....that are not needed on pages there after. CaseWorker and Organisation
    Call ClearExpenses
        With WS
            'lr = .Range("D" & Rows.Count).End(xlUp).Row
            .Range("A8:V32").Copy .Range("A" & Rows.Count).End(xlUp).Offset(2, 0)               'Pastes a copy of the table below current table between the bottom of the table and the totals
            .Range("A" & Rows.Count).End(xlUp).Offset(15, 0).PageBreak = xlPageBreakManual      'Inserts a page break
            .Range("I40").Copy Range("I" & Rows.Count).End(xlUp).Offset(x, 0)                   'Copies the page number function to the newly created page
        End With
End Sub

This is my code to hide the rows.

Sub HideRows()
Dim cell As Range, n As Long, i As Integer, e As Long
e = 0
n = 14
i = 0
    For Each cell In Range("D14:D17")
        If cell.Value = "" Or cell.Value = 0 Then Rows(n).Hidden = True
        n = n + 1
        i = i + 1
    Next cell
        i = 0
        For Each cell In Range("D14:D17")
            If cell.Value = "" Or cell.Value = 0 Then
            i = i + 1
        End If
        Next cell
    If i = 4 Then Rows(13).Hidden = True
n = 20
    For Each cell In Range("D20:D28")
        If cell.Value = "" Or cell.Value = 0 Then Rows(n).Hidden = True
        n = n + 1
    Next cell
End Sub

Thanks

 

[Michael M]

Well, you would know where the next block / s of data would sit, so adjust the code to suit

For Each cell In Range("D14:D17","D42:D46", "D65:D71")'sample ranges only
        If cell.Value = "" Or cell.Value = 0 Then Rows(n).Hidden = True
        n = n + 1
        i = i + 1
    Next cell

 

[Michael M]

Maybe simpler

Sub HideRows()
Dim cell As Range, n As Long
    For Each cell In Range("D14:D17", "D20:D28")
        If cell.Value = "" Or cell.Value = 0 Then Rows(cell.Row).Hidden = True
    Next cell
End Sub

 

[dpaton05]

Thanks for the reply Michael. I do however need all the above code but for each period, however many there are.

 

[dpaton05]

Just having some ideas but I don't know how to code them

• I could find the bottom cell in column D and that will find the bottom of the period at the bottom of the sheet.
• This might prevent having to loop through every row in the sheet.
• I could find the bottom cell in column D, check the cells for 8 rows above it to see if they have a 0 or nothing. If any satisfy the criteria, they are hidden.
• After that is done, there are 2 blank rows between that table and another table above it.
  • For instance, before any periods are added, the tables are A14:I17 and A20:I28
  • Column D will have the cells in the table that will have a 0 or no entry if the row is to be hidden. D14:D17 and D20:D28

• Each period will be the same layout as the previous period.
• Each period is on a separate page
• There are 39 rows between a cell in the first period and the same cell in the second period.
• For every period thereafter, there is a difference of 37 rows

Please reply back and ask me further clarification as I am not sure what you need.

Thanks Michael