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

Saqib
Update-graph-range-by-selection.xlsx
LVL 43
Saqib Husain, SyedEngineerAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

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

Regards
0
 
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

0
 
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.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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.
0
 
Rob HensonFinance AnalystCommented:
Alternative, you can setup a dynamic range using the following formula:

=OFFSET($A$1,VLOOKUP(CELL("row"),$P$2:$R$7,2)-1,0,VLOOKUP(CELL("row"),$P$2:$R$7,3)-VLOOKUP(CELL("row"),$P$2:$R$7,2)+1,2)

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.

Thanks
Rob H
0
 
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")

Thanks
Rob
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Just what I was looking for. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.