Update graph range by selection_change

The attached sheet shows a graph on the data from row 2 to row 11

I need a worksheet selection change macro which would change this range to 13 to 18 when the cursor is within any of those rows.

Thanks in advance

LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Put this into the Sheet1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Target.Row
Case 2 To 11
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("A2:B11")
Case 13 To 18
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("A13:B18")
Case 20 To 22
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("A20:B22")
Case 24 To 26
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("A24:B26")
Case 28 To 30
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("A28:B30")
End Select
End Sub

Open in new window

Saqib Husain, SyedEngineerAuthor Commented:
Hi, thanks for that. But these are not all the ranges and they may also change. I need the program to look at column A and work out the rows to be considered.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Well, all I can do is answer the question you asked, which I have done. I'm sure you can work anything else from there.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.


pls try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <= 2 And Range("B" & Target.Row) <> "" Then
    FirstRow = IIf(Range("B" & Target.Row - 1).Value = "", Target.Row, Range("B" & Target.Row).End(xlUp).Row)
    LastRow = IIf(Range("B" & Target.Row + 1).Value = "", Target.Row, Range("B" & Target.Row).End(xlDown).Row)
    ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("A" & FirstRow & ":B" & LastRow)
End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Alternative, you can setup a dynamic range using the following formula:


In the Range P2:R7 I have set a small table defining the ranges for the data sets:

Row       Start      Finish
2      2      11
13      13      18
20      20      22
24      24      26
28      28      30
32      32      

When the activecell changes the lookup within the offset changes and creates the new range.

Rob H
Rob HensonFinance AnalystCommented:
However, when I change the chart to use DataRange (the dynamic named range), it resets back to a hard coded area; albeit the correct range for the recalculated DataRange.

I assume this line in the Selection_Change event will correct that part:

ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.Range("DataRange")

Saqib Husain, SyedEngineerAuthor Commented:
Just what I was looking for. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.