Selecting Part of a Column from a ListObject table in VBA...?

[450nick]

Hi everyone!

I'm trying to select part of a table using VBA. Basically I'm using a loop to roll through each column of a table, and for certain columns I want to extract the body data (not the header), but only the cells that are populated. The cells will always be filled from the top, so I'm looking for row 2 to row x from the column. I have used the following formula to work out the number of items in the column (in this case 2 when excluding the header) out of the total column length of 10 plus the header.

r = Application.WorksheetFunction.CountA(Worksheets("Data").ListObjects("Scenario" & Scenario & "DC").ListColumns(i).Range)

How on earth do select row 2 to row 4 as a range to pull into a VBA array??

 

[Zot]

I believe this will have everything you need

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[450nick]

I don't see anything in that website that covers this requirement?? I'm currently thinking of using redim preserve but I'm getting an error... Any ideas why??

                r = Application.WorksheetFunction.CountA(Worksheets("Data").ListObjects("Scenario" & Scenario & "DC").ListColumns(i).DataBodyRange)
                
                Choices = Worksheets("Data").ListObjects("Scenario" & Scenario & "DC").ListColumns(i).DataBodyRange
                ReDim Preserve Choices(r)

 

[Zot]

Any sample for helpers outside there to have more understanding on what you want to achieve?