1

I have a table that I copy its data to an array, then I want to add this array back to the last row of the table but to exclude the headers row of the table, everything works but the headers table is still being copied to the array.

 Sub readingarray()


Dim table_list_object As ListObject
Dim table_object_row As ListRow
Dim arr As Variant
Dim tbl As Range

Set tbl = shdata.Range("j17").CurrentRegion
    tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select


arr = tbl


Set table_list_object = shdata.ListObjects("LeaveTracker8")
Set table_object_row = table_list_object.ListRows.Add

Dim rowcount As Long, columncount As Long
 rowcount = UBound(arr, 1)
 columncount = UBound(arr, 2)
table_object_row.Range(1, 1).Resize(rowcount, columncount).Value = arr
End Sub
dima gorokh
  • 37
  • 5

1 Answers1

0

You have:

Set tbl = shdata.Range("j17").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select

Select-ing the range without the headers doesn't change the range (including headers) that tbl refers to.

Change:

arr = tbl

to

arr = tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Value

The .Value is not necessary but better to be explicit here.

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • one more question with this code how would i be able to extract only the filtered cells from the table to the array? – dima gorokh May 07 '21 at 18:05
  • That's a separate question, answered [here](https://stackoverflow.com/questions/11995761/how-do-i-copy-a-filtered-range-into-an-array-excel-vba) and [here](https://stackoverflow.com/questions/36763612/add-visible-cells-of-a-range-to-array) and [here](https://stackoverflow.com/questions/49852197/get-filtered-records-into-array-variant-without-looping-vba) and other places. – BigBen May 07 '21 at 18:07
  • the thing is that I don't want to use an advanced filter because i want to keep the filter with a slicer on the table. and i cant seem to get the SpecialCells(xlCellTypeVisible) to work – dima gorokh May 07 '21 at 18:12
  • Probably deserves asking a new question then, but it might end up being closed as a duplicate. – BigBen May 07 '21 at 18:16