VBA macro to add row to table

[bertilak]

I got a great answer to my previous question (VBA copy from cell range to current location.) which was about how, using a VBA macro, to copy a range of cells to the current cell.

I would now like to improve upon this macro by having the current cell be automatically selected (by the macro) as the first cell below a table. This table automatically extends as new rows are appended to the end. Right now I need to manually select the cell then run the macro. It would be nice if I could automatically position the macro to the cell just below the table.

What I tried:

Range(Table1).EntireRow.Insert
Table1.EntireRow.Insert

And some similar things. All get errors like
Runtime error '1004' Method 'Range' of object _global failed (first line above)
Runtime error '424' Object required (2nd line above)

Note that "Table1" is what the name manager shows and it's the onle table in thedocument.

 

[Joe4]

What columns is your table in?
If column A is the first column in your table, and it always has data for every row in your table, you can use this line to select the first blank row under it (assuming that there is nothing else already under the table):

Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

 

[bertilak]

Let me rephrase the question.

What I really want is a way to make the row just after the end of the table the active row. Then when I do the paste (as in the answer to my original question referenced above) the table should automatically extend to include the new row.

What columns is your table in?
If column A is the first column in your table, and it always has data for every row in your table, you can use this line to select the first blank row under it (assuming that there is nothing else already under the table):

Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

Hi. Thanks.

That worked but I'm not sure I understand it. There are blank rows above the table -- how come this doesn't confuse things?

Wouldn't it be more robust to somehow reference the actual table itself? It has a name ("Table1").

 

[Joe4]

Let me rephrase the question.

What I really want is a way to make the row just after the end of the table the active row. Then when I do the paste (as in the answer to my original question referenced above) the table should automatically extend to include the new row.

Hi. Thanks.

That worked but I'm not sure I understand it. There are blank rows above the table -- how come this doesn't confuse things?

Wouldn't it be more robust to somehow reference the actual table itself? It has a name ("Table1").

What line of code does is start at the very bottom of Excel, in the last possible row (Rows.Count will return the last possible row number in Excel).
By starting at the very bottom and going up, you find the last populated cell in that column (column A in our example).
Then Offset(1 , 0) just moves you one row down from that.

In summary, so what that line of code does is find the last populated cell in column A and then move down to the row underneath that.
As long as you don't have anything under/below your table in column A, this should do what you want.

 

[Alex Blakenburg]

Wouldn't it be more robust to somehow reference the actual table itself? It has a name ("Table1").

There are often multiple ways of doing things in VBA and they all have their strengths and weaknesses.
Here is an option that uses the table name.

Sub TestCopyNewRows()

    Dim lo As ListObject
    Dim newRow As ListRow
    Dim cpyRng As Range
    
    Set cpyRng = Range("I6:J9")
    Set lo = Range("Table1").ListObject
    Set newRow = lo.ListRows.Add
    
    cpyRng.Copy Destination:=newRow.Range.Cells(1)
    
End Sub