Hide entire Table if cells empty

Hi.
I can find plenty of examples on how to hide rows of a table when empty using VBA but nothing to hide the entire table is the cells within that table are empty

Can anyone give me a example on how to achieve this?

try

Sub HideEmptyTables()
    Dim tbl As ListObject
    
    For Each tbl In ThisWorkbook.Worksheets("Sheet1").ListObjects
        
        If WorksheetFunction.CountA(tbl.Range.Offset(1)) = 0 Then
            tbl.Range.EntireRow.Hidden = True
        End If
    Next tbl
End Sub

Originally Posted by k1dr0ck

try

Sub HideEmptyTables()
    Dim tbl As ListObject
    
    For Each tbl In ThisWorkbook.Worksheets("Sheet1").ListObjects
        
        If WorksheetFunction.CountA(tbl.Range.Offset(1)) = 0 Then
            tbl.Range.EntireRow.Hidden = True
        End If
    Next tbl
End Sub

Not working for me. code doesn't do anything, no error

In this case, the table is not empty. Probably because of formulas that return an empty string.
Try this

Sub HideEmptyTables2()
  With ActiveSheet.ListObjects(1)
    
    If Len(Application.Concat(.Range.Offset(1))) = 0 Then
      .Range.EntireRow.Hidden = True
    End If
    
  End With
End Sub

Artik

curious why it does not do anything i tried it on a sample table where the rows are empty(not deleted) except the headers and it does hide the table

Thanks for the replies.
i made a new spreadsheet with a table and seems that the code works however doesn't work with my current worksheet

Not sure if it has anything to do that the table in comes from a Power Query connection?

could you provide your sheet with desensitized data?

See attached

found a code here in the forum
it filters the table showing only cells with values in column A so when all cells are blank in A then all cells are hidden although the headers are still visible
is this what your desired output is?

Sub test()
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<>"
End Sub

The desired output would be to have every element of the table to be hidden including the headers and the total row

from your attached sheet the table has a single cell with a value so its not actually empty
my first code was for a table that is empty except the headers
maybe someone can provide what your desired output is with the attached sheet

The first table was a test to see if the formula works
The second table has the query connection. This is the one that i would like the formula to work with

Code works fine for the first table (when the cell data is deleted), but not for the second (when the data is deleted)

tried my code on post 2 on your attachment and it hides the second table

i cleared the contents of all the cells of the second table not including the headers then ran the code

Finally diagnosed the problem

Seems that Artik's code works only if there is no totals row for the table
Is there a way to incorporate this into the code?

Try this:

Sub HideEmptyTables3()

    With ActiveSheet.ListObjects(1)
        If .ListRows.Count > 0 Then
            If Len(Application.Concat(.DataBodyRange.Value)) = 0 Then
                .Range.EntireRow.Hidden = True
            End If
        Else
            .Range.EntireRow.Hidden = True
        End If
    End With
    
End Sub

Now you see how important it is to attach an adequate example to your question (because the one attached does not reflect the layout of your actual data). Three days of foam-balling what could have been dealt with in one day.

Artik

I do apologise
I made the attached without the totals row and didn't notice.
Ill make more of a effort next time

If i was to do this to multiple tables, would there be a more efficient way of doing this or is repeating the code with a different ListObject Number the best/only way?

Yes, there are fans of Ctrl+C and Ctrl+V shortcuts in the world, but you can also think of a loop, such as:

Sub HideEmptyTables4()
    Dim i           As Long

    With ActiveSheet
        For i = 1 To .ListObjects.Count
            With .ListObjects(i)
                If .ListRows.Count > 0 Then
                    If Len(Application.Concat(.DataBodyRange.Value)) = 0 Then
                        .Range.EntireRow.Hidden = True
                    End If
                Else
                    .Range.EntireRow.Hidden = True
                End If
            End With
        Next i
    End With
End Sub

or

Sub HideEmptyTables5()
    Dim LO          As ListObject

    For Each LO In ActiveSheet.ListObjects
        With LO
            If .ListRows.Count > 0 Then
                If Len(Application.Concat(.DataBodyRange.Value)) = 0 Then
                    .Range.EntireRow.Hidden = True
                End If
            Else
                .Range.EntireRow.Hidden = True
            End If
        End With
    Next LO

End Sub

Artik

Is there also a way to hide the first row ABOVE the table if the table is hidden?

Perfect, works well

Thank you for your help Artik.
Ill close the thread

No security. It seems to be enough to change line

.Range.EntireRow.Hidden = True

to

.Range.Offset(-1).Resize(.Range.Rows.Count + 1).EntireRow.Hidden = True

Artik

This works great. But i assumed it would unhide the row Above when the table is not empty?

I guess I don't understand the problem.

Artik

no you were correct

I made an assumption that the row above would hide when there was data in the table & unhide when there was no data in the table

Is this possible?

Maybe

Sub HideEmptyTables6()
    Dim i           As Long
    Dim blnCondition As Boolean

    With Me

        For i = 1 To .ListObjects.Count

            With .ListObjects(i)
                If .ListRows.Count > 0 Then

                    blnCondition = (Len(Application.Concat(.DataBodyRange.Value)) > 0)

                    .HeaderRowRange.Offset(-1).EntireRow.Hidden = blnCondition
                    .Range.EntireRow.Hidden = Not blnCondition

                Else
                    .HeaderRowRange.Offset(-1).EntireRow.Hidden = False
                    .Range.EntireRow.Hidden = True
                End If
            End With

        Next i

    End With
End Sub

Artik

Thanks for your response
However it shows a "Compile error" Invalid use of Me keyword

Replace Me with ActiveSheet.

Artik

Although mostly working

The code doesn't unhide the row above the tables that are visible

It hides all the every row above every table instead whether the table is visible or not

Originally Posted by beyond Excel

Hi. It seems to me that the following VBA code covers all angles of the problem:

Sub Macro8()
Dim tb As ListObject, Rng As Range, cHidden As Boolean
'-------------->
For Each tb In ActiveSheet.ListObjects
  If tb.HeaderRowRange.Row > 1 Then
    Set Rng = Union(tb.HeaderRowRange.Offset(-1), tb.Range)
  Else
    Set Rng = tb.Range
  End If
'-------------->
  Select Case True
    Case tb.ListRows.Count = 0: cHidden = True
    Case Else
      If Application.CountBlank(tb.DataBodyRange) = tb.DataBodyRange.Count Then cHidden = True
  End Select
  Rng.EntireRow.Hidden = cHidden
Next
'-------------->
End Sub

This just hides all the tables from what i can see

Artik is very close. Just need to figure how to unhide the Row above tables that are visible

As soon as i run the code everything is hidden. Including tables with Data in them