Hiding tables with VBA

[lealea1982]

Hello all,

I'm getting myself all confused trying to do this one so any help would be massively appreciated!!

I have a large workbook with 30 worksheets. The worksheets are named 01, 02, 03 etc, all the way up to 30.

On each worksheet there are a varying number of codes using the letters of the alphabet (i.e. on page 01 I have codes from 01A up to 01K, on page 02 I have codes from 02A up to 02H, etc etc). Each code has it's own table: always 10 columns and always a varying number of rows (starts off as 10 rows but users will insert or delete rows as they go along). The first row is a subtotal of the rows underneath. That makes it sound quite complicated so I've attached an example of how it looks.

The key thing to remember is that there is a different number of codes per worksheet (but that is set and users won't be able to add or remove codes) and each code may need a differing number of rows per table.

What I need is a macro to look at each table (within that sheet) and, if it's empty of data, hide it (hide the rows). I tried defining the name of each table and then setting it to work through each name, but there are 417 codes in total - so it got very long-winded!

I know this is quite complicated, but if anyone has any suggestions I'd be SO grateful!! I'm entirely self (and forum) taught on Excel so I'm aware there may be something relatively obvious that I just haven't thought about!

Many thanks

[Domski]

Hi,

It would be useful if your example showed completed and empty tables along with how you would like things to look once the code has executed.

Dom

[lealea1982]

Absolutely, here it is.
The first sheet shows what each page (01 through to 30) would look like before running the macro, the second page shows how it would look after (the table containing no data are hidden rows).

Bearing in mind each page will have a different number of tables.

Hope that helps

[Domski]

Hi,

Try this:

Please Login or Register  to view this content.

Dom