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 · Accepted Answer · 2013-12-26 02:20:57Z

0

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

answered Dec 26, 2013 at 2:20

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

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

2 Comments

user3091646 Over a year ago

Thanks for your idea ,the last column is located at "E column" (dynamic, maybe F,G...column),because the statistics could not display,and the field like "2013/Q3",that have 2 type, the other type like "2013/4m",so I want to get the range from "B1" to the last address of column type1 or type2

Jeremy Thompson Over a year ago

Thats why I showed the Set rng = Range(Selection, Selection.End(xlToRight)).Select twice.

Collectives™ on Stack Overflow

Excel vba get dynamic range( fields) to create chart

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related