Inserting rows in a table

[Pat Jordan]

I have a workbook (Excel 2010) with three tables in one of the worksheets. After working fine for some time, it will now not allow me to add rows to the first table. The first 5 lines of my procedure to do this are:

Sub AddMaterialRow()
Dim iTableRows As Integer, i As Integer
iTableRows = ActiveSheet.ListObjects(1).ListRows.Count
ActiveSheet.Unprotect
ActiveSheet.ListObjects(1).ListRows.Add

One reaching line 5, it gives the error message: Run-time error ‘1004’: This operation is not allowed. The operation is attempting to shift cells in a table on your worksheet.

The equivalent subroutines for the other two tables still work correctly.
Table 1 will also no longer allow me to insert table rows manually, although I can do so for the other two tables. Attempting to insert a row in the table gives me a pop-up text box containing the same error message text as given by VBA (without the error number). The table contains a mix of locked and unlocked cells, but that should not matter with the worksheet unprotected.
I made some changes to table 3 between the previous version that works and the current version with this problem, but table 3 still works fine in both versions.
What can be preventing me from inserting rows in table 1? Any ideas would be much appreciated.

[mrice]

Have you got any data out of sight in the very bottom row of your worksheet? Excel can object if an insert shifts filled cells off the edge.

[muymalestado]

As mrice implies sometimes finding data way outta sight can be tricky - try to delete whole rows / columns but watch out for your good cells.

Now, did you use a sensible layout to create 3 tables on 1 worksheet?

Try to always create new table areas in a diagonal manner so no row nor column of any table overlaps any other table. Separate by several rows / columns. Use subtle horizontal / vertical marking to indicate top-to-bottom and left-to-right where your mini table boundaries are.

It is sooo easy to lose track of where your tables are.

[Pat Jordan]

Thanks for the idea mrice. However that's not the problem here as I don't have data beyond about the first 50 rows. See my reply to muymalestado for the solution.

Pat Jordan

[Pat Jordan]

Thanks muymalestado, you have helped me find the key to the problem.

The three tables need to be under each other to minimise horizontal scrolling on data entry. All start in column A. In order from the top, they span columns A:K, A:L and A:G. The problem arose after I added two columns to the second table which was previously over A:J. It turns out that all is fine provided that no table is wider than any table above it.

The solution was simple: I added a dummy column to the top table to make it the same number of coulmns as the second table. I could alternatively have reversed the positions of the top two tables.

Thanks very much, Pat Jordan