Excel 2007. Moderate understanding of VBA code.
Hi all,
I am looking to make a graph that can vary its start date (history) based on a user defined cell.
I have a graph and the coding that will allow it to select the source data range from a defined start point to the end of the data - thus allowing for more data to be added.
Dim lastRow As Long
With Sheets("Debt 90-119 Days")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=.Range("B1:F" & lastRow)
ActiveChart.Axes(xlCategory).Select
End With
This creates a graph with source data from B1 through to F and down as far as the lowest row with data.
I would like the B1 (as the start point) to be variable and defined upon a different cell (A1).
A1 will consist of a number that matches the row I want the range to start from.
A1 = 5. Data range = B5:F (last row).
Essentially I need the range to be: B(A1):F(last row).
Can anyone help?
Thanks.
Hi all,
I am looking to make a graph that can vary its start date (history) based on a user defined cell.
I have a graph and the coding that will allow it to select the source data range from a defined start point to the end of the data - thus allowing for more data to be added.
Dim lastRow As Long
With Sheets("Debt 90-119 Days")
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=.Range("B1:F" & lastRow)
ActiveChart.Axes(xlCategory).Select
End With
This creates a graph with source data from B1 through to F and down as far as the lowest row with data.
I would like the B1 (as the start point) to be variable and defined upon a different cell (A1).
A1 will consist of a number that matches the row I want the range to start from.
A1 = 5. Data range = B5:F (last row).
Essentially I need the range to be: B(A1):F(last row).
Can anyone help?
Thanks.