1

I am trying to have my code refer to ranges only through tables (so that if I add or remove a column from a table, the code will not change). I have a table that is called MyTable. This table has a header 'MyHeader'.

Here I get the last last row of the table:

dim numRecords as integer
numRecords  = sheet1.listobjects("MyTable").listRows.count

Now I have a function that receives a range type argument and puts something in this range.

I want the function to receive the cell that is on column 'MyHeader' and row number numRecords . How would I get the range of this cell (with referring only to the table and not to the rows or columns of the sheet)?

Thanks

Hana
  • 89
  • 2
  • 7

2 Answers2

1

You can use something like:

With Sheet1.ListObjects("MyTable").ListColumns("MyHeader").DataBodyRange
    .Cells(.Rows.Count, 1).Value = "test"
End With
Rory
  • 32,730
  • 5
  • 32
  • 35
  • thanks. What if I want a range that is bigger than one cell? For example, column "MyHeader" rows 1:5? – Hana Dec 14 '19 at 17:35
1

@Rory did answer my question about referring to a specific cell within a table. With the help of [this question][1] I figured how to refer to a range of cells within a table, in case this is helpful to anyone:

Dim tbl As ListObject
dim StartRow as integer

startRow = 2
Set tbl = Track.ListObjects("MyTable")

tbl.ListColumns("MyColumn").DataBodyRange.Offset(startRow).Resize(tbl.ListRows.Count - startRow ) = "SomeValue"
Hana
  • 89
  • 2
  • 7
  • The link I wanted to add above: https://stackoverflow.com/questions/45447968/reference-only-part-of-an-excel-table-data-body-range-with-vba – Hana Dec 14 '19 at 18:40
  • Suggest to see these documentation pages: [ListObject object (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.listobject) [ListColumn object (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.listcolumn) – EEM Dec 18 '19 at 17:46