Hide entire Table if cells empty

[neek0la]

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?

[k1dr0ck]

try

Please Login or Register  to view this content.

[neek0la]

try

Please Login or Register  to view this content.

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

[Artik]

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

Please Login or Register  to view this content.

Artik

[k1dr0ck]

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

[neek0la]

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?

[k1dr0ck]

could you provide your sheet with desensitized data?

[neek0la]

See attached

[k1dr0ck]

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?

Please Login or Register  to view this content.

[neek0la]

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

[k1dr0ck]

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

[neek0la]

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)

[k1dr0ck]

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

[neek0la]

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?

[Artik]

Try this:

Please Login or Register  to view this content.

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

[neek0la]

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

[neek0la]

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?

[Artik]

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

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Artik

[neek0la]

Perfect, works well

Thank you for your help Artik.
Ill close the thread

[neek0la]

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

[Artik]

No security. It seems to be enough to change line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Artik

[neek0la]

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

[Artik]

I guess I don't understand the problem.

Artik

[neek0la]

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?

[Artik]

Maybe

Please Login or Register  to view this content.

Artik

[neek0la]

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

[Artik]

Replace Me with ActiveSheet.

Artik

[neek0la]

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

[beyond Excel]

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

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

Please Login or Register  to view this content.

[neek0la]

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

Please Login or Register  to view this content.

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

[beyond Excel]

And it also shows you the tables as soon as they start to have data: Is it verified?

[neek0la]

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

[beyond Excel]

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

Please: upload your sample workbook with the implemented code on the forum to see the same thing you are seeing...