Extracting data from tables (ListObjects) in Microsoft Excel is very easy within Excel, but within VBA it is rather cumbersome. You can only refer to one column or one row at a time, and there is no way to refer to an individual cell other than by taking an intersection.

The Find method only works on ranges, not tables, so it is cumbersome to search tables because you get trapped dealing with ranges and lose all the benefits of using tables. Also, to find all but the first match, the Find method requires a loop and repeated calls to the FindNext or FindPrevious methods.

I’ve written two excellent functions for dealing with table data in VBA. You can download them here.

TableRange returns a range from a table as the intersection of one or more sections (HeaderRowRange,
DataBodyRange, InsertRowRange, and TotalsRowRange), one or more columns (by name), and/or one or more rows (by numbers). This makes it very simple to address any portion of a table in VBA with a single line of code.

Example: Return all cells in the Client column of the Timeslips table.

tblTimeslips.ListColumns("Client")

– versus –

TableRange(tblTimeslips, "Client")

Example: Return all cells in the Client and Date columns of the Timeslips table.

Union(tblTimeslips.ListColumns("Client"), tblTimeslips.ListColumns("Date"))

– versus –

TableRange(tblTimeslips, Array("Client", "Date"))

Example: Return all cells on row 4 of the Timeslips table.

tblTimeslips.ListRows(4).Range

– versus –

TableRange(tblTimeslips, , 4)

Example: Return all cells on rows 4, 5, and 6 of the Timeslips table.

Union(tblTimeslips.ListRows(4).Range, tblTimeslips.ListRows(5).Range, tblTimeslips.ListRows(6).Range)

– versus –

TableRange(tblTimeslips, , Array(4, 5, 6))

Example: Return cells in Client and Date columns of the Timeslips table from row 4.

Intersect(Union(tblTimeslips.ListColumns("Client"),tblTimeslips.ListColumns("Date")), tblTimeslips.ListRows(4))

– versus –

TableRange(tblTimeslips, Array("Client", "Date"), 4)

TableFind returns a range from a table as the intersection of one or more columns (by name) and any rows matching a specified search value (as formatted) in a specified search column (by name). Compound search criteria can be achieved by taking intersections/unions of multiple TableFind functions.

Example: Return all cells from the Date and Amount columns of the Timeslips table where the corresponding Client column matches "ABC*" and the corresponding Billed column matches "False".

Intersect( _

TableFind(tblTimeslips, "Client", "ABC*", Array("Date", "Amount"), _

TableFind(tblTimeslips, "Billed", "False", Array("Date", "Amount") _

)

Intersect( _

TableFind(tblTimeslips, "Client", "ABC*"), _

TableFind(tblTimeslips, "Billed", "False"), _

TableRange(tblTimeslips, Array("Date", "Amount")) _

)

I’ve also added a clsTable class with interfaces to the TableRange and TableFind
functions, and a Table function for instantiating clsTable objects, thus enabling the abbreviated syntax below:

With Table(tblTimeslips)

Set rng = Intersect( _

.Find("Client", "ABC*"), _

.Find("Billed", "False"), _

.Range(Array("Date", "Amount")) _

)

End With