DataBodyRange lost after adding new column to listobject

[mdwasim]

Hello Experts,
I am not able to figure out why the Run-time error.
For every row in the 1st table, a new table is created.
The new tables will consist of the number of rows as per the #Count column from 1st table.
Also, a new column will be added at the end of the table if column "t1col5 to t1col8" value is "Yes".

Upon, execute, the table to be created is copied from Templates sheet.
If the #Count is more than "1" it works fine!!.

When #Count is "1" i.e., the new table will have 1 Row, and this is where the with block of Range.validation throws error 91.
I tried quite a few tweaks, but none worked.


With 4-5 hours of head scratching, What I understood is a single Cell Column is not considered as "DataBodyRange"

If thats the case, why is it so and how to work around this issue?

It would be really helpful if someone can take a look at the code and point me in right direction.

Tables.xlsm

Shared with Dropbox

www.dropbox.com

Thanks.

 

[mdwasim]

Looking at the number of unanswered questions, I don't see any time soon I will be getting a review

 

[John_w]

With 4-5 hours of head scratching, What I understood is a single Cell Column is not considered as "DataBodyRange"

If thats the case, why is it so and how to work around this issue?

Maybe some facts about tables will help you. DataBodyRange is Nothing when the table has no data rows. An empty table has a header row (HeaderRowRange) and 1 row of empty cells below the header row, so although the table appears to have 1 data row, that data row is empty and therefore DataBodyRange is Nothing. DataBodyRange is defined (i.e. not Nothing) only when the table has at least 1 row of data.

 

[mdwasim]

Wow, this was really a new thing for me. Never knew that is the case.
Thank you, John!! For this information.

A couple of work around that comes to my mind are
1. To use Table range and offset/resize by 1 row.
2. Add a "space" to the first cell of the row in of the template table and call a function to clear the DataBodRrange on each table as the last step of the process.

I do see additional overhead of calling a function #2 as compared to #1 i.e., doing it the right way (if it works)
I will give it a try.

 

[John_w]

I haven't looked at your code, so I don't know what the correct approach should be. One way is an If statement which checks if DataBodyRange Is Nothing:

    Set table1 = ActiveSheet.ListObjects("Table1")
    If table1.DataBodyRange Is Nothing Then
        'Table1 has 1 'data' row which is empty
    Else
        ':
    End If