CODEWITHSUNDEEP
vbaexcel
Windmill
Windmill

Reputation: 163

Get a dynamic range of a column in a table

I'm trying to find a way of getting a range from of a table for certain column dynamically. By this I mean getting the first row and finding the end row (keep in mind I need to find the end row dynamically).

Something similar to using 

.End(xlUp).Row

but for a Table column instead.

Upvotes: 1

Views: 154

Answers (1)

John Coleman
John Coleman

Reputation: 52008

A table will be a member of the worksheet's ListObjects collection. You can do something like this:

Sub test()
    Dim table As ListObject
    Set table = ActiveSheet.ListObjects("Table1")
    Debug.Print table.ListColumns(2).Range.Rows.Count
End Sub

Note that ListObjects have a ListColumns collection whose entries can be turned into ranges.

The following documentation is helpful: https://msdn.microsoft.com/EN-US/library/office/ff839458.aspx . See also this tutorial: http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

Upvotes: 3

Related Questions