Search code examples
arraysexcelvbarowslistobject

Capture specific rows of a ListObject to an array

I need help trying to figure out something I haven't done before in Excel VBA. I'm trying to assign specific (consecutive) rows of a listobject to an array. Is this possible?

Previously I was using code to copy over the entire table into the array, which worked, but it would capture unused blank rows I didn't want included. I was able to get some FUNCTIONS up and running which capture the first and low filled row numbers of the listobject for reference, but I cannot figure out how to assign ONLY this range of rows from the listobject into the array

This, for instance, works to assign only the 1st row: varESInvoiceBreakdown = PrimeLO.ListRows(1).Range.Value

However, this doesn't work to capture the first two rows. I get an error varESInvoiceBreakdown = PrimeLO.ListRows("1:2").Range.Value

Even though it indicates in a segment of this article that you can reference multiple rows in this manner https://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

I was hoping to reference the two integer variables I have setup for the row selection.

Sincerely, Kris

Solution

  • You can't index into the ListRows collection like that. Use DataBodyRange.Rows.

    varESInvoiceBreakdown = PrimeLO.DataBodyRange.Rows("1:2").Value

    With variables:

    Dim startRow as Long
Dim endRow as Long

startRow = 1
endRow = 2

varESInvoiceBreakdown = PrimeLO.DataBodyRange.Rows(startRow & ":" & endRow).Value