VBA defining a range from an array

Question

So I have a 2d array with 1000 rows and 10 columns. I want to create 10 ranges for each of the 10 columns and then use them as series to make a chart. MY question is, how do I define a range from an array, without using the entire array, just one column in it?

Sub ChartLord()

    Dim rows As Long
    Dim columns As Integer
    Dim mychart As Chart
    Dim data As Range
    Dim dataset() As Double

    rows = ShData.Cells(ShData.rows.Count, 1).End(xlUp).Row
    columns = ShData.Cells(1, ShData.columns.Count).End(xlToLeft).Column

    'set array range (includes column titles and xAxix column)
    dataset = ShData.Range(ShData.Cells(1, 1), ShData.Cells(rows, columns))



    For Z = 0 To 10

        Set data = ?
        Set mychart = shtCharts.Shapes.AddChart2(200, xlColumnClustered, 50 + 300 * Z, 50, 300, 200, 5).Chart


    Next Z



End Sub

You should understand that both rows and columns are reserved words in VBA and should not be used to name variables. — user4039065
– user4039065, Commented Apr 5, 2016 at 18:24

user4039065 · Accepted Answer · 2016-04-05 18:34:36Z

4

The following will collect your range values into a 2D range then slice off the second column into a new 2D array. The latter is simply 1 to 1 'column' wide.

Dim rws As Long, cols As Long, d As Long
Dim dataset() As Variant, subdataset() As Variant
Dim ShData As Worksheet

Set ShData = Worksheets("Sheet4")

rws = ShData.Cells(ShData.rows.Count, 1).End(xlUp).Row
cols = ShData.Cells(1, ShData.columns.Count).End(xlToLeft).Column

'set array range (includes column titles and xAxix column)
dataset = ShData.Range(ShData.Cells(1, 1), ShData.Cells(rws, cols))

subdataset = Application.Index(dataset, 0, 2) '<~~second column
For d = LBound(subdataset, 1) To UBound(subdataset, 1)
    Debug.Print subdataset(d, 1)
Next d

ShData.Cells(1, "Z").Resize(UBound(subdataset, 1), UBound(subdataset, 2)) = subdataset

The last operation puts the values of the peeled off column back into the worksheet starting at Z1.

answered Apr 5, 2016 at 18:34

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

Duranchula Over a year ago

thanks for that, would you be willing to chat with me in the chat forum on this site? I just have a few questions about graphing series in vba.

Collectives™ on Stack Overflow

VBA defining a range from an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related