Variables in Arrays in VBA Excel

I am building a macro to chart reports created by COGNOS. Unfortunatly these reports are supplied by another team.

The data for the charts comes as Excel but the headers might be changed each report and the number of data sources also change.

What this means is that in one开发者_Go百科 week the data for monday to friday might include 6 sources eg. Columns A to G (Mon - Fri) and Rows 1 to 6

The next data table is produced by COGNOS 2 rows below this eg. Row 8

but the next week there are 8 data sources, so the table is that much longer and the second table is at Row 10

Using an array can I fix the charts to collect there data from a header cell for each table where ever it is placed on the excel supplied?

Use this function to count the rows of data you need to chart. Pass it a refrence to the first element in the table, and it will retrun the row count.

Public Function CountRows(ByRef r As Range) As Integer
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.count
    End If
End Function

To select an multiple rows,cols use Range("A2").Resize(20,8) for example to expand the range from "A2" to include 20 rows and 8 columns. The rest you put together programmatically by counting rows and columns and selecting appropriate values for your charts. Note that it is best to set the .XSeries and YSeries properties of a chart Series using the RC style. Example: .YSeries="='Sheet1'!R2C3:R101C3" where you set the apropriate text values based on what you measure for the tables.