Use a table name instead of fixed range in a function

[BradleyS]

I have a table of data in one column called "MyData" and currently set as Worksheets("Data").Range("H39:H49")
The objective is to get the column of data into a comma separated variable

However, I don't seem to be able to find a way to use the table name in my transpose formula below

I'm trying to substitute the range to use a table name, as the table is dynamic, so the range will change

How do I covert this formula (which works fine) to use my table name?
Dim x As Variant
x = Join(Application.WorksheetFunction.Transpose(Worksheets("Data").Range("H39:H49").Value), ",")

as the below doesn't work, and various other methods I have tried to use.
x = Join(Application.WorksheetFunction.Transpose(MyData.Value), ",")

 

[Beneindias]

Hi.

Try this:

Worksheets("Data").ListObjects("Table_Name").ListColumns("MyData")

You need to swap "Table_Name" for the name of your table

 

[BradleyS]

x = Join(Application.WorksheetFunction.Transpose(Worksheets("Data").ListObjects(MyData).Value), ",")
Shows error "Script out of range"

 

[Beneindias]

x = Join(Application.WorksheetFunction.Transpose(Worksheets("Data").ListObjects(MyData).Value), ",")
Shows error "Script out of range"

I edited my reply to you.
Change that. It should point to the column range

 

[BradleyS]

The table and column of data is the same, and still throws an error
x = Join(Application.WorksheetFunction.Transpose(Worksheets("Data").ListObjects("MyData").ListColumns("MyData").Value), ",")

 

[Beneindias]

I don't think that you can use the property Value with listcolumns.

Maybe DataBodyRange

 

[Alex Blakenburg]

Try:

Sub ConvertData()

    Dim tbl As ListObject
    Dim x As Variant
    
    Set tbl = Range("MyData").ListObject
    x = Join(Application.Transpose(tbl.ListColumns("MyData").DataBodyRange.Value), ",")
End Sub