Setting values in table

Question

I have an Excel table named Master_Table with eight columns, column D being a calculated column.

I deleted all the rows in the table except the header row and the first data row.

This code works.

Worksheets("Master List").Activate
Set tbl = ActiveSheet.ListObjects("Master_Table")
'Delete all Master_table rows except first row
With tbl
    If Not .DataBodyRange Is Nothing Then
        .DataBodyRange.Delete
    End If
End With

I have also written data into a .csv file in a two dimensional array, Player_array, that has 24 columns and 4100 rows.

I want to add rows and assign values to Master_Table, but the first assignment statement gives a run time error:

Object variable or with block variable not set.

num_rows = UBound(Player_array, 1) - LBound(Player_array, 1) + 1
For R = 0 To num_rows
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("ACBL #")).Value = Player_array(R, 1)
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("First Name")).Value = Player_array(R, 4)
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("Last Name")).Value = Player_array(R, 6)
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("Masterpoints")).Value = Player_array(R, 13)
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("Phone 1")).Value = Player_array(R, 15)
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("Phone 2")).Value = Player_array(R, 16)
    tbl.DataBodyRange.Cells(R, tbl.ListColumns("Email")).Value = Player_array(R, 22)
    tbl.ListRows.Add
Next R

You deleted the `DataBodyRange` so now it is `Nothing`, the `tbl` doesn't have one. — BigBen, Aug 14 '20 at 20:56
https://stackoverflow.com/questions/20663491/delete-all-data-rows-from-an-excel-table-apart-from-the-first/46316955 — Tim Williams, Aug 14 '20 at 21:07

score 0 · Answer 1 · answered Aug 14 '20 at 21:20

Here's how to add rows to a table and populate them

Dim rng As Range
'...
num_rows = UBound(Player_array, 1) - LBound(Player_array, 1) + 1
For r = 0 To num_rows
    With Worksheets("Master List").ListObjects("Master_Table")
        Set rng = .ListRows.Add.Range 'add a new row and get its Range
        rng.Cells(.ListColumns("ACBL #").Index).Value = Player_array(r, 1)
        rng.Cells(.ListColumns("First Name").Index).Value = Player_array(r, 4)
        'etc etc
    End With
Next r

Thanks. I ended up doing approximately that. – BROnstott Aug 16 '20 at 22:11 — BROnstott, Aug 16 '20 at 22:11

score 0 · Answer 2 · answered Aug 16 '20 at 22:12

Thanks. I ended up doing about as you suggested. Here's what I ended up with, which works well.

 Worksheets("Master List").Activate
    Set tbl = ActiveSheet.ListObjects("Master_Table")

    '  Delete all Master_table rows except first row
    With tbl.DataBodyRange
        If .Rows.Count > 1 Then
            .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
        End If
    End With

    '  Resize Table to add new rows
    tbl.Resize tbl.Range.Resize(tbl.Range.Rows.Count + num_rows - 1, tbl.Range.Columns.Count)

    '  Copy data from Player_Array into Master_Table
    num_rows = UBound(Player_array, 1) - LBound(Player_array, 1) + 1
    With tbl.DataBodyRange
        For R = 1 To num_rows
          .Cells(R, 1).Value = Player_array(R - 1, 0)
          .Cells(R, 2).Value = Player_array(R - 1, 3)
          .Cells(R, 3).Value = Player_array(R - 1, 5)
          .Cells(R, 5).Value = Player_array(R - 1, 12)
          .Cells(R, 6).Value = Player_array(R - 1, 14)
          .Cells(R, 7).Value = Player_array(R - 1, 15)
          .Cells(R, 8).Value = Player_array(R - 1, 21)
          Next R
    End With
End Sub

Setting values in table

2 Answers2