We help IT Professionals succeed at work.

Update graph range by selection_change

Saqib Husain, Syed
on
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
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
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

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 Consultant
Awarded 2014
Top Expert 2014

Commented:
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.
Top Expert 2016
Commented:
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
Rob HensonFinance Analyst

Commented:
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
Rob HensonFinance Analyst

Commented:
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
Just what I was looking for. Thanks.