Can't insert or delete table rows.

**kjg** · 03-01-2023, 02:00 PM

Hi Guys,
I am posting in the General as I am getting the issue with or without vba.

Attached is a cutdown version of a workbook that has been working fine for more than 5 years.

I have added records to the cutdown version so far without any problems (so far).

Today on the full version of the workbook after adding about 15 records spread through the 4 tables using the ?Add Record? button, I get a debug with ?Run-time Error 1004? ?This won?t work because it would move cells in a table on your worksheet?.

If I ?End? and try to insert a table row OR delete a table row I get a dialog ?This won?t work because it would move cells in a table on your worksheet?

I can still add records to previous sheets.

Each sheet in the full workbook is a copy of the previous sheet with the data removed, the cutdown version sheet is also a copy of its previous months sheet but with the previous months sheet deleted.

The debug is in the frmDataEntry .ListRows.Add AlwaysInsert:=True

   Set lo = ActiveSheet.ListObjects(x)   'set ListObject (lo) to the Table Selected above (x)                            
                            With lo  'With that List Object (x)
'Allow the Table to grow
                                .ListRows.Add AlwaysInsert:=True

Any ideas why this should have happened?

**kjg** · 03-04-2023, 01:18 PM

Anyone any ideas please?

**kjg** · 03-09-2023, 05:37 AM

Well over 100 people have looked at this over the past week, but no suggestions

**avk** · 03-09-2023, 06:38 AM

Why not 4 table in 4 different sheet & calculate in summary sheet with pivot table.
In 4 different sheet add column card payment & statement date.

**kjg** · 03-09-2023, 08:34 AM

Thank you very much for showing an interest.

We have 4 tables on the sheet for the 4 catagories and a sheet for each month, so at least 60 sheets so far. This is how the owner/user and her accountant requires the data.

**torachan** · 03-09-2023, 08:43 AM

You are mixing the 'cosmetics' with the 'practicalities' forget about the 'pretty' data storage.
Store your data in one table, then extract it to a 'report template' in that manner you will eliminate the messy insertion of rows in tables stacked (not a good idea).
Your data storage table needs to be as formatted in sheet1 - I notice in attached file a sheet at Sheet126 - if this is the case your file must be 'bloated'.
One simple table could contain a thousand years of information that could be extracted to a report at any time - apply the 'KISS' principle to data management.
My guess is that Excel is falling over because some limit has inadvertently been reach - e.g. look at formula in 'D31' has any of the elements exceeded the 255 character string length ???

**kjg** · 03-09-2023, 09:27 AM

Hi Torachan,
Thank you for your reply.
I understand exactly what you are saying about having all the data in 1 table and it does make perfect sense. I will have the conversation with the owner/user of the workbook. Before I do, I will create a new workbook as your sheet1 and import data form the last few months to demonstrate how it would look and work.
I also think you are right with the formulas exceeded the 255 character.
I will now mark the thread as solved.
Kind Regards
Keith

**TMS** · 03-09-2023, 08:48 AM

Well over 100 people have looked at this over the past week, but no suggestions

Well, that can happen.

You've posted in General which means that some people who don't know VBA might look and back away immediately. Then, there might be some, like me, who make a lot of responses on my iPad and phone ... no VBA. And there might be some who can't replicate the problem and don't want to clutter the thread up. Lots of reasons for not replying. And, once you've bumped the thread, it won't show up in unanswered threads which some folk go to to see what, well, hasn't been answered.

Anyway, all that said, I have now opened your workbook on my Windows Laptop. Security on Windows prevents the opening of macro enabled workbooks as it flags (blocks) them and you have to manually remove the block.

So, I have tested the basic premise, can I insert and delete rows in the Table, and it seems I can. The implication being that although you may not be using VBA to add rows, something is interfering with the process.

As you have stated that you can add records to the cut down version of the spreadsheet, I suppose it is only to be expected that I can too, albeit without VBA.

From a practical point of view, you need to upload a copy of the workbook that demonstrates the problem with clear instructions as to how to replicate the issue.

**kjg** · 03-09-2023, 09:27 AM

Hi TMS,
Thank you for your reply.
I originally posted in the general forum as the problem was present with no vba involved, although I did include the same problem with vba code included…..my mistake, I should have posted in the VBA forum.
I take your point about “Bumping” won't show up in unanswered threads, I never even considered that.
I have decided to take the advice of Torachan in the above post and will now mark the thread as solved.
Kind Regards,
Keith

**TMS** · 03-09-2023, 09:42 AM

Can't argue with the advice torachan has offered. In my mind, it is always best to have ALL the raw data in a single flat (normalised) table on one sheet. Add a column(s) for the date and/or month to allow differentiation of records. You can filter, sort and chart the data. You can also analyse the data using Pivot Tables and Pivot Charts. With O365, you have a vast array of new functions that will allow you to extract and manipulate the data.

And, if I didn't mention it before, the more sheets you have, the more difficult it becomes to manage them … and even find what you are looking for.

Good luck.

**TMS** · 03-09-2023, 12:37 PM

You're welcome. Thanks for the rep.

**torachan** · 03-09-2023, 01:15 PM

thanks for the feedback and added rep point

Can't insert or delete table rows.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Re: Can't insert or delete table rows.

Thread Information

Users Browsing this Thread

Similar Threads

Cannot delete rows or columns, cannot insert table (says tables cannot overlap).

[SOLVED] Worksheet change event to exclude the Insert Rows and Delete Rows

Table that insert measures and insert blank rows

[SOLVED] VBA script to Insert and Delete rows at the end of a named table

[SOLVED] VBA insert/delete rows to accommodate exact number of rows from incoming data

Insert / Delete Rows depending upon Dynamic Built Table

Delete Rows based on cell value and then Insert rows alternatively with a formula

Bookmarks

Bookmarks

Posting Permissions