Hiding title rows of empty data charts

Question

I have a series of worksheets that show values from Vlookups. I have created a sub to hide all the empty rows within the tables but I am unsure how to hide the title rows if all the rows beneath it are hidden. For the hidden row function I use color and the value in four of the columns to determine whether or not to hide the row using a for loops with the number of rows. The row hide code is as shown.

Public Sub RowHide()
    Application.ScreenUpdating = False

    'variable declarations
    Dim ws As Worksheet
    'column number ref.
    Dim r As Integer
    r = 6
    Dim num As Integer
    'To go through Each WS
    For Each ws In ActiveWorkbook.Worksheets
        Dim i As Integer
        Dim plusVar As Integer
        Let plusVar = 2
        'To go through the rows
        With ws
           For i = 1 To 200
               .Rows(i).AutoFit

            If (.Cells(i, r).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                If (.Cells(i, r - 1).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                    If (.Cells(i, r + 1).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                        If (.Cells(i, r + plusVar).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                            .Rows(i).Hidden = True
                        End If
                    End If
                End If
            End If

            On Error Resume Next
            Next i

        End With

        On Error Resume Next
    'adjusting the target column for each Ws
        r = r - 1
        If r = 4 Then
            r = 3
            plusVar = 3
        End If

    Next ws
    Application.ScreenUpdating = True


End Sub

This code works perfectly. The next step is to have a sub that would check if the rows under each "title" are all hidden (If there is any data present in the table I don't want to hide the title) and if so then hide the row with the title on it.

This is the formatting of the table:

 A          B         [Status]
**TITLE**  BlankCell Not Hidden (what I want to hide)
Category1  BlankCell Row Hidden
Category2  BlankCell Row Hidden
Category3  BlankCell Row Hidden
Category4  BlankCell Row Hidden
Blank Cell BlankCell Row Not hidden

Example of other possibility:

A          B         [Status]
**TITLE**  BlankCell Not Hidden (Don't want to hide it in this scenario)
Category1  BlankCell Row Hidden
Category2  BlankCell Row Hidden
Category3  BlankCell Row Hidden
Category4  Value     Row not hidden
BlankCell  BlankCell Not hidden (was trying to use this to determine range of what to hide)

This is the code I have so far for the title row hide sub:

Public Sub UnusedTitleHide()

    Dim ws As Worksheet
    Dim rw As Range
    Dim LastRow As Long
    Dim i As Integer
    Dim b As Integer

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
            Dim firstRow As Long
            Dim endRng As Range
            Dim endRow As Long
            Dim hide As Boolean
            Dim hiC As Integer
            Dim hiT As Integer
            hiC = 0
                For i = 1 To LastRow
                    If (.Cells(i, 1).Value <> 0 And .Cells(i, 1).Text <> "") Then
                        Set endRng = .Cells(i, 1).End(xlUp).Offset(1)
                        Let endRow = endRng.Row
                    End If
                b = i
                hiC = 0
                    Do While b <= endRow
                        hiT = endRow - b
                            If .Rows(b).Hidden = True Then
                                hiC = hiC + 1
                            End If
                        b = b + 1
                    Loop
                    If hiC = hiT Then
                        If i - 1 <> 0 Then
                            If (.Cells(i - 1, 1).Text = "") And hiC = hiT Then
                                .Rows(i).Hidden = True
                            End If
                        End If
                    End If

                    On Error Resume Next

                    Next i
        End With
    Next ws

End Sub

Edit: Clarifying formatting of the worksheet.

    A          B         [Status]
    **TITLE**  BlankCell Not Hidden (what I want to hide as no categories have values.)
    Category1  BlankCell Row Hidden
    Category2  BlankCell Row Hidden
    Category3  BlankCell Row Hidden
    Category4  BlankCell Row Hidden
    Blank Cell BlankCell Row Not hidden
    **TITLE**  BlankCell Row Not hidden (Don't want to hide this row as a category has values)
    Category1  Text/Num  Row Not Hidden 
    Category2  BlankCell Row Hidden
    Category3  Text/Num  Row Not Hidden
    Category4  BlankCell Row Hidden 

Answer 1

This is the final rendition of the code based on E. Merckx's "Better Edit"

Public Sub UnusedTitleFinal()
Dim ws As Worksheet
Dim Cell As Range
Dim Rng As Range
Dim lastRow As Long
Dim rngString As String
Dim TitleCell As Range
Dim i As Integer

'Iterating through each worksheet   
For Each ws In ActiveWorkbook.Worksheets

    With ws
        'Finding and setting the range to check the cells
        lastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
        rngString = "A" & "6" & ":" & "A" & lastRow + 1
        Set Rng = .Range(rngString)
        For Each Cell In Rng.Cells

            If Cell.Font.Bold = True Then
            Set TitleCell = Cell
                i = 1
                Do While Cell.Offset(i, 0).Rows.EntireRow.Hidden = True
                    i = i + 1
                Loop

                If IsEmpty(Cell.Offset(i, 0)) = True Then
                    TitleCell.Rows.EntireRow.Hidden = True
                End If

            End If
            'Next Cell
        Next Cell
   End With
Next ws

End Sub

Answer 2

One method to do this is to check each row in your data table. If any rows are not hidden (i.e., have data), then don't hide the title row. Otherwise, if all the rows are hidden, then hide the title row. Assuming that your title row is the first row:

For Each ws In ActiveWorkbook.Worksheets

    With ws
    LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

    For i = LastRow To 2 Step (-1) 'from the end to one below the title row

        If .Rows(i).Hidden = True Then
            'Hidden Row. Keep checking for data
        ElseIf .Rows(i).Hidden = False Then
            'Data is present, no need to hide title
            GoTo NextSht
        End If

    Next i

    'No Data was found, so hide the title row
    .Rows(1).Hidden = True 

NextSht:
    End With

Next ws

---

BETTER EDIT: The below code looks for the title rows. It loops to the next unhidden row, and if it is blank, hides the title row. Let me know if this one works for you:

For Each Cell In rng
    If Cell.Font.Bold = True Then
    Set TitleCell = Cell
        i = 1
        Do While Cell.Offset(i, 0).Rows.EntireRow.Hidden = True
            i = i + 1
        Loop

        If IsEmpty(Cell.Offset(i, 0)) = True Then
            TitleCell.Rows.EntireRow.Hidden = True
        End If

    End If
    'Next Cell
Next Cell

---

EDIT: The revised code will look through a set of cells, if they are separated by one empty line, and provided that there are no blanks in between. It further assumes that the lines are hidden based on whether or not there is a value in the right adjacent cell of the "categories."

Public Sub UnusedTitleHide()

Dim ws As Worksheet
Dim LastRow As Long

Dim LocalLastRow As Long
Dim LocalFirstCell, LocalLastCell, LocalCells As Range

On Error GoTo ErrHandle

WSCount = ActiveWorkbook.Worksheets.Count

'I was getting errors when using the "For Each ws" loop.
'It failed to move onto the next worksheet, for some reason
For W = 1 To WSCount
    Set ws = ActiveWorkbook.Sheets(W)

    With ws
        'A more reliable way of finding the last row
        LastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

        'Unhide everything so the End function doesn't skip cells.
        For i = 1 To LastRow
            Rows(i).Hidden = False
        Next i

        'Start with the first cell
        Set LocalFirstCell = .Cells(1, 1).Offset(1, 0)


        Do While LocalLastRow < LastRow
            'Last cell is at the end of the section
            Set LocalLastCell = LocalFirstCell.End(xlDown)
            'Set is made up of cells between first and last
            Set LocalCells = Range(LocalFirstCell, LocalLastCell)

            For Each Cell In LocalCells
                If IsEmpty(Cell.Offset(0, 1)) = True Then
                    Cell.Rows.EntireRow.Hidden = True
                End If
            Next Cell

            For Each Cell In LocalCells
                If Cell.Rows.EntireRow.Hidden = True Then
                    'HiddenRow. Keep Checking for data
                ElseIf Cell.Rows.EntireRow.Hidden = False Then
                    'Data is present, no need to hide title
                    GoTo NextSet
                End If
            Next Cell

            'No Data Was found, so hide the title row
            LocalFirstCell.Offset(-1, 0).Rows.EntireRow.Hidden = True

NextSet:
            LocalLastRow = LocalLastCell.Row
            Set LocalFirstCell = LocalLastCell.End(xlDown)
            'Catch if the End function goes to the end of the sheet
            If LocalFirstCell.Row = 1048576 Then
                GoTo NextWorksheet
            Else
                'Otherwise keep on looping
                Set LocalFirstCell = LocalFirstCell.Offset(1, 0)
            End If

        Loop

NextWorksheet:
        End With

    Next W

ErrHandle:
        'Triggered by the Find function not finding anything
        If Err.Number = 91 Then
            MsgBox ("No Data in " & ws.Name)
            Resume NextWorksheet
        End If

End Sub

While this may work for your current situation, as @Mooseman suggested, it may be best to hide the title row while you are hiding all the other rows.

Answer 3

Since the rows are hidden only if you get to the last if statement, as true, in your nested ifs, then any else would not hide the row and leave the headers, so:

  If (.Cells(i, r).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
            If (.Cells(i, r - 1).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                If (.Cells(i, r + 1).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                    If (.Cells(i, r + plusVar).Value = 0 Or .Cells(i, r).Text = "") And .Cells(i, r).Interior.ColorIndex < 0 Then
                            .Rows(i).Hidden = True
                    Else
                    Titlesvisable = 1
                    End If
                Else
                Titlesvisable = 1
                End If
            Else
            Titlesvisable = 1
            End If
        Else
        Titlesvisable = 1
        End If