Search code examples
excelexcel-2013vba

Adding a row to an Excel table using VBA without enabling total row

I have a table (called PortDistances) showing distances between pairs of ports. I've got it set to not show a totals row, because it's simply not relevant to the data in it.

I also have a UserForm to add a line of data to the end of the table, using this code:

Private Sub AddOne_OK_Click()
Dim lrow As Long
lrow = Worksheets("Port distances").Cells(Rows.Count, 1).End(xlUp).Row
' Add new port pair distance
Worksheets("Port distances").Cells(lrow + 1, 1).Formula = "=" & Worksheets("Port distances").Cells(lrow + 1, 2).Address(False, False) & "&" & Worksheets("Port distances").Cells(lrow + 1, 3).Address(False, False)  ' Extend formulas
Worksheets("Port distances").Cells(lrow + 1, 6).Formula = "=" & Worksheets("Port distances").Cells(lrow + 1, 1).Address(False, False) & "&" & Worksheets("Port distances").Cells(lrow + 1, 4).Address(False, False)  ' Extend formulas
Worksheets("Port distances").Cells(lrow + 1, 2).Value = strFrom  ' POL
Worksheets("Port distances").Cells(lrow + 1, 3).Value = strTo  ' POD
Worksheets("Port distances").Cells(lrow + 1, 4).Value = Val(Box_DistNew.Value)  ' Distance
Worksheets("Port distances").Cells(lrow + 1, 5).Value = "Schedule App"  ' Source

Unload Me
End Sub

Annoyingly, every time I run it, the added line becomes a totals row, usually asking me if I want to overwrite existing total row formulae (bearing in mind, again, that there was no totals row to start with).

I've tried adding Worksheets("Port distances").ListObjects("PortDistances").ShowTotals = False to turn it back into a normal line, but that just wipes that row. On the other hand, manually clearing the "Totals row" checkbox just turns it into a normal line.

Why does it take it as a totals row, and how do I stop it?

Solution

  • You can use Worksheets("Port distances").ListObjects("PortDistances").Listrows.add to add the new row, then populate it. For example:

    Dim lo                    As ListObject
Dim lr                    As ListRow
Dim n                     As Long

Set lo = Worksheets("Port distances").ListObjects("PortDistances")
Set lr = lo.ListRows.Add
For n = 1 To lo.ListColumns.Count
    lr.Range(1, n).Value = "item " & n
Next n