Hiding rows in multiple ranges

[ac1911]

I'm trying to hide rows in multiple ranges in a sheet. I can't seem to get it to work completly. Listed below is the code. I've defined the sections of what I want to hide and I want to only hide the rows that are 6(7) rows in and stop at 'Total'. I was able to get this work partially but not anymore. I was also never able to hide the rows in the closings section.

Can someone take a look and see what I'm missing. Any help is much appreciated.

Sub Hide_Sum_Unused_Rows_Starts()
'
Dim Category2 As String
Dim Row_Start As Integer
Dim RngToSum As Range
'
'Hide Rows in Starts
'
Range("Starts.Summary").Select
Row_Start = ActiveCell.Row + 7
'
'Check for values in row and hide if zero
'
Do Until Category2 = "Total"
Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AZ" & Row_Start)
    If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
        Range("A" & Row_Start).Select
        Selection.EntireRow.Hidden = True
    Else
    End If
Loop
'
'Sales Section
'
Set RngToSum = Sheets("Summaries").Range("Sales.Summary")
    If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
        Range("Sales.Summary").Select
        Selection.EntireRow.Hidden = True
    Else
        Range("Sales.Summary").Select
        Row_Start = ActiveCell.Row + 6
'
        Do Until Category2 = "Total"
        Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AC" & Row_Start)
            If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
                Range("A" & Row_Start).Select
                Selection.EntireRow.Hidden = True
            Else
            End If
Loop
'
'Closings Section
'
Set RngToSum = Sheets("Summaries").Range("Closings.Summary")
    If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
        Range("Closings.Summary").Select
        Selection.EntireRow.Hidden = True
    Else
        Range("Closings.Summary").Select
        Row_Start = ActiveCell.Row + 6
'
        Do Until Category2 = "Total"
        Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AC" & Row_Start)
            If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
                Range("A" & Row_Start).Select
                Selection.EntireRow.Hidden = True
            Else
            End If
Loop
'
'Lot Closings Section
'
Set RngToSum = Sheets("Summaries").Range("Lot.Closings.Summary")
    If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
        Range("Lot.Closings.Summary").Select
        Selection.EntireRow.Hidden = True
    Else
        Range("Lot.Closings.Summary").Select
        Row_Start = ActiveCell.Row + 6
'
        Do Until Category2 = "Total"
        Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AC" & Row_Start)
            If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
                Range("A" & Row_Start).Select
                Selection.EntireRow.Hidden = True
            Else
            End If
Loop
End Sub

 

[Von Pookie]

I have only glanced quickly at the code at the moment, but you do not appear to be setting 'Category2' anywhere. How can the code loop "Do Until Category2 = "Total" if Category2 is never set/changed?

 

[ac1911]

I've been trying different things to get the code to work and I believe when it was partially working I had the following after End If for each section

Row_Start = Row_Start + 2
Range("A" & Row_Start).Select
Category2 = Range("A" & Row_Start).Value
Loop
Category2 = ""
Row_Start = 0

 

[ac1911]

I've modified the code and here is the latest version. I can get the first two sections to work but when it gets to the closings it does not work at all and when it gets to the Lots it hides everything.

Sub Hide_Sum_Unused_Rows_Starts()
'
Dim Category2 As String
Dim Row_Start As Integer
Dim RngToSum As Range
'
'Hide Rows in Starts
'
Range("Starts.Summary").Select
Row_Start = ActiveCell.Row + 7
'
'Check for values in row and hide if zero
'
Do Until Category2 = "Total"
Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AZ" & Row_Start)
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("A" & Row_Start).Select
Selection.EntireRow.Hidden = True
Else
End If
Row_Start = Row_Start + 1
Range("A" & Row_Start).Select
Category2 = Range("A" & Row_Start).Value
Loop
Category2 = ""
Row_Start = 0
'
'Sales Section
'
Set RngToSum = Sheets("Summaries").Range("Sales.Summary")
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("Sales.Summary").Select
Selection.EntireRow.Hidden = True
Else
Range("Sales.Summary").Select
Row_Start = ActiveCell.Row + 6
'
Do Until Category2 = "Total"
Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AC" & Row_Start)
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("A" & Row_Start).Select
Selection.EntireRow.Hidden = True
Else
End If
Row_Start = Row_Start + 1
Range("A" & Row_Start).Select
Category2 = Range("A" & Row_Start).Value
Loop
End If
'
'Closings Section
'
Set RngToSum = Sheets("Summaries").Range("Closings.Summary")
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("Closings.Summary").Select
Selection.EntireRow.Hidden = True
Else
Range("Closings.Summary").Select
Row_Start = ActiveCell.Row + 6
'
Do Until Category2 = "Total"
Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AC" & Row_Start)
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("A" & Row_Start).Select
Selection.EntireRow.Hidden = True
Else
End If
Row_Start = Row_Start + 1
Range("A" & Row_Start).Select
Category2 = Range("A" & Row_Start).Value
Loop
End If
'
'Lot Closings Section
'
Set RngToSum = Sheets("Summaries").Range("Lot.Closings.Summary")
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("Lot.Closings.Summary").Select
Selection.EntireRow.Hidden = True
Else
Range("Lot.Closings.Summary").Select
Row_Start = ActiveCell.Row + 6
'
Do Until Category2 = "Total"
Set RngToSum = Sheets("Summaries").Range("A" & Row_Start & ":AC" & Row_Start)
If Application.WorksheetFunction.Sum(RngToSum) = 0 Then
Range("A" & Row_Start).Select
Selection.EntireRow.Hidden = True
Else
End If
Row_Start = Row_Start + 1
Range("A" & Row_Start).Select
Category2 = Range("A" & Row_Start).Value
Loop
End If
'End of Procedure Cleanup
'
Row_Start = 0
Category2 = ""
End Sub