Excel vba get dynamic range( fields) to create chart

Question

Sheet2
sheet2

I export an excel with a table in sheet1,and have a graph in sheet2, but now I have one problem, the table was generated from store procedure,the fields:"2013/Q1 2013/Q2 2013/Q3 2013/Q4 " was dynamic AND also the statistic values which display by user choose from webpage,now,the exported graph (Line Chart) ,its X-axis includes the statistic values that I dont need the fields( sheet2 ), so I write VBA code like :

> Sheet2.ChartObjects("Chart 1").Activate
>     ActiveChart.ChartArea.Select
>      Sheets("Sheet1").Range("A1").Clear
>      ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B1",Sheets("sheet1").Range("B1").End(xlDown).End(xlToRight).Address),
> PlotBy:= _
>        xlRows
>     ActiveWindow.Visible = False

I want to get the range "B1" to "E11"( the cell is dynamic,so we dont get the position of the cell exactly), and all the title fields and data of row and column were dynamic , how can I write the correct VBA to do it, thanks ps,sorry , poor English

Jeremy Thompson Jeremy Thompson · Accepted Answer · 2013-12-26T02:20:57

Write a for loop in VBA that detects the last column, assuming the column headers will always be located on row 1 and the data starts on column B:

Sub FindRange()

Dim i As Integer
Dim rng As Range
Range("B1").Select
Set rng = Range(Selection, Selection.End(xlToRight)).Select
Set rng = Range(Selection, Selection.End(xlToRight)).Select

For i = 2 To Range(rng.Column & 1).Column

Set rng = Range("A" & i)
    If rng.Value2 = "SUM" Then
        'We've found the end column i = 11
        Exit For
    End If

Next i

End Sub

Excel vba get dynamic range( fields) to create chart

1 Answers