Adding Data to a Excel Table

Question

I am struggling to find out how to add data to a table. I have the following code:

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")
tbl.ListRows.Add 1

This adds a new row at the top of the table but how do you then add data to the specific columns in that new table row?

Column 1 in the table = Date
Column 2 in the table = Licence 1
Column 3 in the table = Licence 2
Column 4 in the table = Licence 3

I know if I wanted to add a new row to the bottom of the table then I would use this:

Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")
Set NewRow = tbl.ListRows.Add
With NewRow
    .Range(1) = date
    .Range(2) = 378
    .Range(3) = 678
    .Range(4) = 897
End With

But cannot not get it to work for a specific row on the table

Thanks

BigBen · Accepted Answer · 2020-05-05 17:49:18Z

1

Change

Set NewRow = tbl.ListRows.Add

to

Set NewRow = tbl.ListRows.Add(1)

If you don't specify the position, the row will always be added at the end. (You had it right in the first snippet.)

answered May 5, 2020 at 17:49

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andy M Over a year ago

i've been searching for a good couple of hours trying to get it to bloody work, cheers fella.

Andy M Over a year ago

can you set an existing row in the table ?

BigBen Over a year ago

Set existingRow = tbl.ListRows(1)?

Collectives™ on Stack Overflow

Adding Data to a Excel Table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related