CODEWITHSUNDEEP
excelvba
Luvidia
Luvidia

Reputation: 11

Write new row to table

I created a table in a Excel sheet (named Table1) with a header and a blank row. I can display it in a listBox on my form.
If I add a cell in the Excel table, I can display new cells when my script is running.

When I try to add a new cell with VBA, I have an error.

Dim MyTable as ListObject 
Dim newRow as ListRow

Set myTable = Sheets("Sheet1").ListObjects("Table1")

nbLine = myTable.ListRows.Count

myTable.ListRows.Add

When I run this, nbLine writes the right number of lines of my table. But, when I run the ListRows.Add, I have the error

'Method 'Add' of object 'Listrows' failed'

and Excel crashes.

I tried myTable.ListColumns.Add and it works. It creates a column at the end of my table.

Why doesn't ListRows.Add work?

Also after creating a row, I want to add content to each column of my new row. I didn't find any command to do that. I found on the Microsoft documentation the property DataBodyRange but it only works in reading, not in writing.

How can I write to my table?

Upvotes: 0

Views: 133

Answers (1)

Luvidia
Luvidia

Reputation: 11

Finally, I tried on a new workbook and it works fine. After renaming the new worksheet, it works. But after renaming the table, it didn't work. I then found that if I put an underscore '_' in the name of the table, the instruction listrows.add wont work. If I delete the underscore, the instruction works fine ! My problem appears because I put an underscore into my table's name.

After searching more similar subjects, I found this one: Excel VBA adding new row in table (excel crashing)

The listrows.add wont work because the table is linked to a combobox. If you break the link in the form and create the link by code when launching the application, everything will work !

Upvotes: 1

Related Questions