Two Times Table

So if you’ve played around with Tables a fair bit, then you probably would have noticed that you can merrily insert a row in the worksheet that intersects one table:
Insert OK

…but if you try the same thing on a row that intersects more than one table, the Insert, Delete, and Clear Contents options are grayed out (or greyed out, if you live where I live):
Insert Not OK

You may have tried to get around Excel’s veto by cutting or copying a row from somewhere else, then trying to insert it via the ungrayed (or ungreyed) Insert Copied Cells option:
Insert Copied Cells
…at which point Excel wiped that smirk of your face with this:
Not allowed

So I know when this happens. But I don’t understand why. Anyone have any ideas?

7 thoughts on “Two Times Table

  1. Does Excel possibly store tables as an array, which I know it doesn’t like manipulating unless you change the whole array (sometimes). It might have a work-around for single tables but it throws a hissy fit and doesn’t understand when you try to do 2 at a time.

    By the way – that’s a lovely shade of green you have on your error windows!

  2. “Why” is a metaphysical question, no? IOW, I can’t give a definitive technical reason but I can make wild guesses based on assumptions.

    I’ll assume MS programmers use published methods as building blocks to implement functionality. Since Tables are ListObjects I’ll further assume the Resize method is used when inserting/appending rows/columns into a Table.

    Best guess I have is MS programmers, as bright and wonderful as they are, ran into issues they couldn’t resolve when trying to execute the Resize method (a) on more than x tables or (b) in some other condition beyond my minor imagination. Two may have worked, three as well, but at some point there may have been a wall they hit.

  3. Rich: Yes, quite calming shade. I call it Kermit Green. Possibly intentional on MS’s part.

    I tend to think this greying out is an out-and-out oversight, given it won’t even let you use the right-click Clear Contents option, but will let you happily push Delete on the keyboard itself to clear contents.

    Andrew: I felt immensely better after clicking on the Was this information helpful? link, because apparently my response affects future updates of this information and is returned to Microsoft through the Customer Improvement Program. Plus clicking through actually removed the link from the original Error message, thus removing any distraction from that calming Kermit Green. Which I needed more than ever.

    Oli: Thanks…I forgot to mention that. But what a pain in the ass.

  4. Hi Bigger Don. Thanks for the comment. If that were the case, then it sure would have been nice if they implemented a workaround, such as inserting rows independently in each table, then inserting rows in the spaces between tables. I can’t see any explanation of this behavior on the net anywhere, so would also be good if MS put something in their knowledge base on this…a LOT of users must be impacted by this. Tables are one of the best thing to happen to Excel in years, but this type of mysterious functionality deprecation might put users off adopting them.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.