CODEWITHSUNDEEP
excelvba
Alexander
Alexander

Reputation: 63

Get the range (or values) of a table column Excel VBA

I am trying to get the cell range of the second column of a table in Excel using VBA code, and to automatically update this range if there are new entries in the table every time a userform initializes. At first I thought this would be easy, but right now I'm completely lost and can't figure out how to make my code work. There might be something I'm missing.

I searched on this forum and found two possible solutions, the first one is:

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

This code kinda works. It successfully recognizes the Table1 with all new rows every time I run it. The issue is that it identifies the entire table, I just want to the get the range of a single column. No matter what I do, the variable table always gets the range of the whole table.

I found a second approach as follows:

Sub test()
    Dim tbl as ListObject
    Dim rng as Range
    Set tbl = ActiveSheet.ListObjects("Table1")
    Set rng = tbl.ListColumns(2).DataBodyRange
End Sub

This code also kinda works, but only if the table has a single row of data. If my table only has a single row of data the variable rng identifies the table and row without issues. However, the value of the variable is the value of the cell, like "Sample Text", and not the cell range. And if I try to add any more rows the variable rng remains empty.

I would really appreciate any guidance with this. I should also mention that I want to get the range of a column that belongs to said table even when actively using a different worksheet, like C3:C5. And when more data is entered to get an updated range like C3:C80. I don't want to select the range neither, just get that information and use it for other purposes.

Upvotes: 5

Views: 49758

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

Your code already returns all of the Data for the specified column. You can see this by adding to your Sub (after Set rng = ...), and look at the output in the Immediate Window

Sub test()
    Dim tbl as ListObject
    Dim rng as Range
    Set tbl = ActiveSheet.ListObjects("Table1")
    Set rng = tbl.ListColumns(2).DataBodyRange

    Debug.Print rng.Address
    Dim cl As Range
    For Each cl In rng
        Debug.Print cl.Address, cl.Value
    Next
End Sub

As to your 2nd question, change

Set tbl = ActiveSheet.ListObjects("Table1")

to

Set tbl = ActiveWorkbook.Worksheets("NameOfTableSheet").ListObjects("Table1")

You may want to specify the specific Workbook too.

Set tbl = Workbooks("NameOfWorkbook").Worksheets("NameOfTableSheet").ListObjects("Table1")

Upvotes: 8

Related Questions