I am trying to add multiple rows to my Excel table (the number to be specified by an inputbox). The rows should go in starting at the current cursor position. I want to add Table rows, not worksheet rows.



My first problem was that I found myself unable to identify the current table row using [#This Row], so have had to resort to using absolute row numbers, subtracting the header row from the current worksheet row to give me the table row number. Using this, I can add a single row at my required location with this line of code: However, the code simply exits at this point, so I can't use a For...Next or Do...Until loop to add multiple rows.

Is there a way to add multiple table rows with a single command, or to make this code run round a loop?

CJ, since no one else is answering this I'll take a whack at it, not because I know the answer but because I expect I can find out; I'm good at reading manuals. But I have to ask a silly question first: What do you mean by "table"? I mean, table rows are worksheet rows, aren't they?

I'm not challenging you with what I think is a mistake on your part; you aren't the only one who talks about "tables" as though they're something more than just a place on a worksheet where you've stored columns and rows of data. But it's past time I stopped and asked: If it's something more, then what is it, exactly? Up 'til now, I've been content to put data in a range on a worksheet, and if another worksheet looks up data there I call it a table. But there's nothing special in its definition. You seem to have something else in mind.

I don't doubt there is a way to do VBA statements to manipulate the data. But why wouldn't a simple row insert do?

Maybe I should see your workbook.

Bob: Thanks for getting back to me on this.

In Excel 2007, and more so in 2010, tables have undergone some very interesting developments. Prior to v2007, a table was pretty much a named range, formatted in a cohesive manner. I seldom used them, finding that I had much more control if I did my own formatting.

But with v2010 there has been a great deal of improvement, making it (theoretically) easier to identify and work with headers, the Total row, the data area and the separate fields (columns). The table automatically extends when you add new data, and its range name adjusts accordingly - even if data is added below the table. Filtering is very powerful and offers many options. If you insert a row, formulae are automatically added where necessary. I have come to find tables both attractive and useful.

It is also possible to have two tables side-by-side and to add or delete rows from one without affecting the other. If you right-click on a cell or range within a table, there are options to "Insert table rows above" or "Delete table rows".

These tables do seem to have a lot of new syntax, and it's very hard to track down among all the other stuff on the Internet. I have found a good starting point here: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

In my case, I don't have tables side-by-side so I could use worksheet row additions, and probably will go back to that. But in some cases working with the table really simplifies matters, and I was just trying to find a way of using the new functionality efficiently.

It's just annoying that having found the syntax to add table rows (ListRows.Add as in my original post) it doesn't behave sensibly - just adds a row and stops running the code.

Ho hum.

Hm. I'm not sure I'll be able to help you. I sometimes suspect that MS Office has just gotten so big, and so sophisticated, that they're no longer able to keep up with the bugs; I have found some odd behaviors from time to time that no one seems willing to explain or to fix. Maybe I'm doing them all an injustice.

And of course we all have our own jobs to perform. But if you care to post your workbook, maybe I'll get curious and see whether I can figure something out. No promises, because your description of how it's behaving is clear enough so I suspect it'll simply do the same thing for me and I won't come up with a brilliant fix. But maybe I'll see something you didn't; that's what a fresh pair of eyes is for, after all.

Hi CJ I have attached a Word file with a suggested soultion and would be pleased to have your comments. I hope it's not too far off the mark. As we are both Table fans as well as novices we may be able to help each other along in the future.

Attached Files

Multiple Table Row Inserts.docx (14.9 KB, 23 views)

MikeC, thanks for your help. That's a very interesting document. But I still have a problem. I have used Bob Bridges suggestion and added multiple rows using EntireRow.Insert; I have also tried to add a single row with Selection.ListObject.ListRows.Add (intCurRow - intHeader). In both of these instances, my code simply stops running after completing this line. Therefore I cannot put it in a For... Next... or Do Until... loop or carry out any further instructions.

I have found that even if I add table rows manually, it is not copying the formulae down, possibly because the formulae offer suggested values which are frequently overwritten. I therefore need to add my rows, and continue by entering the formulae into the appropriate columns of the new rows.

Any idea why my code stops as soon as I insert a row?

CJ, I'm reading that article you posted, with an idea of trying out tables and, who knows?, maybe even trying some VBA on a sample table to see whether I encounter the same problem you're having. But I just noticed something in the article that got me wondering: What are the chances that a) you have the auto-expansion option on, and b) if you turn it off, your VBA programs will work normally?

I'm talking about this from the article:
I haven't found yet how I could switch off the behavior, but that's one possibility.

[Later:]Here it is:Another idea: If the table is automatically expanded, I wonder whether the right way to insert a new row in the table is simply to have your program add a row adjacent to it, not within the table's range but in the row just below.

Attention CJ and Bob Bridges. I expect we're not allowed bad words here but I just wrote several lines in response to your latest went off in the middle to look something up and came back to an empty message box.
Anyway CJ you say the " program stops running" after the add line. Have you put the add line in a loop?
for RowNo = 1 to 10
Range(myTable).ListObject.ListRows.Add (intCurRow - intHeader)
Range(myTable).cells(intCurRow-intHeader,1).Value="Value for Col. 1 in the New Row"
next RowNo
Will add 10 rows at table row (intCurRow-intHeader) putting the same dull text in the 1st col of each one. The 1st entry will end up 10 rows below the last entry. Apologies for not using the Selection. option but if you no where the new row is to be all you need use is the table name.
If that's not what you want why don't you put your worksheet up on the forum.
I notice that Bob Bridges refers to an article you posted. I haven't seen it. Was it under a different thread? And on the topic of different threads I did post one today about excel table formula problems. If either of you have seen it I would appreciate some feedback.

I took these to mean so. Indeed it seems to be the whole point of this thread:
Naw, it's below in the 2013-10-25 18:28 post; the link is http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp.

Hello MikeC and Bob and thanks again for your input.
Bob, I followed your advice and have tried it with the "Include new rows and columns in table" both checked and unchecked, but continue to see the same problem: no code is executed after the initial row is inserted.

My plan was to have the table sorted in date order, but with some forthcoming payments entered; I therefore wanted the user to place the cursor on the first FUTURE date and insert a row (or multiple rows determined by an input box) at the cursor position.

MikeC, when I say the code stops, it just stops executing. I can be in the middle of a loop, stepping through with the yellow bar, but when I get to the command that enters a new row, the yellow bar goes off and all further lines of code are simply not executed. If I continue to press F8, I get a yellow highlight at the start of the Sub again. If I run the code without stepping through, one line is entered if I use Range(myTable).ListBbject.ListRows.Add, and n rows are added if I use Selection.EntireRow.Insert, but no actions beyond that point occur - not even a simple message box.

I'm working on diminishing returns here - as has been mentioned, I could get the user to add the data to the bottom of the table, then sort it into date order. I'll try one last thing - copying the module and table into a new workbook. If that doesn't work, I might strip it down to a sample file and post it up here, but I don't really want to waste any more of your time.

There's a scene in NCIS where a skilled hacker is playing in one of Abbey's computers, and she's frantically trying to shut him out. She and McGee are shouting advice at each other, but neither knows what to do because the Bad Guy seems to be better than both of them and he's getting at their databases. What will they do? They seem to be helpless—

—and then the screen goes black, and their hopelessly untechnical boss stands up, brandishing the now-disconnected PC's power plug.

Along those lines, is it worth suggesting that you abandon tables and have your VBA program work with ... well, just cells? At least until you figure out (or Microsoft fixes) this problem.

HI CJ Im sorry if my question assumed the worst but I tend to think that everyone is as dumb as myself when it comes to waging syntactical war on badly behaved software. I did run a few test with Bob's .EntireRow but couldn't raise an error from it; what I did notice is that it Inserts a sheet row not a table row and although the effect on the table is the same it would disupt any stuff to the right of the the table. Bob I think there may well be a lot of sense in what you say about tables especially as they do not always behave as expected. How long would you estimate for MS to put them right?

<Shrug> How should I know? We're not even sure it's a problem, yet. At least I'm not; since I've never used tables there's a lot I don't know about them, so maybe this is just a wrinkle and tomorrow CJ will realize he wasn't holding his mouth right. And if it is a bug, Microsoft has to notice the problem first, which may mean someone will have to call it to their attention and I have no idea how to go about doing that. After that it may not take too long, maybe a few months and then the next release.

I'm all relaxed about this because I don't use tables anyhow :-). I'd probably share a lot of his frustration if it weren't for that.