excel - vba get range of specific cells in a table (listobject)

Question

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

标签： excelvbalistobject

Answer 1

You can use something like:

With Sheet1.ListObjects("MyTable").ListColumns("MyHeader").DataBodyRange
    .Cells(.Rows.Count, 1).Value = "test"
End With