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

450nick

Well-known Member
Joined
May 11, 2009
Messages
505
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??
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,004
Office Version
  1. 2016
Platform
  1. Windows
I believe this will have everything you need
 

450nick

Well-known Member
Joined
May 11, 2009
Messages
505
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??


VBA Code:
                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

Well-known Member
Joined
Nov 26, 2020
Messages
2,004
Office Version
  1. 2016
Platform
  1. Windows
Any sample for helpers outside there to have more understanding on what you want to achieve?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,824
Messages
5,766,657
Members
425,367
Latest member
Boboka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top