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?
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
Please Login or Register to view this content.
In this case, the table is not empty. Probably because of formulas that return an empty string.
Try thisArtikPlease Login or Register to view this content.
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?
Please Login or Register to view this content.
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: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.Please Login or Register to view this content.
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:orPlease Login or Register to view this content.
ArtikPlease Login or Register to view this content.
Perfect, works well
Thank you for your help Artik.
Ill close the thread
Is there also a way to hide the first row ABOVE the table if the table is hidden?
No security. It seems to be enough to change linetoPlease Login or Register to view this content.
ArtikPlease Login or Register to view this content.
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?
MaybeArtikPlease Login or Register to view this content.
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
Hi. It seems to me that the following VBA code covers all angles of the problem:
Please Login or Register to view this content.
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
And it also shows you the tables as soon as they start to have data: Is it verified?
As soon as i run the code everything is hidden. Including tables with Data in them
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks