PDA

View Full Version : dynamic x axis "find last cell" vba code



banane78
02-18-2013, 05:01 AM
hi guys,

I would like to change the macro as follows:
I modify the X axis by this code but would like to make it dynamically in terms of defining the last cell "'INPUT SHEET'!R13C1:R23C1"
Since the last cell is not always 23 I want to change it saying search the last cell and use this new range as X axsi label. How can I change the code to get this procedure work?

Private Sub CommandButton1_Click()
Sheets("CHART-Output").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='INPUT SHEET'!R13C1:R23C1"
ActiveChart.SeriesCollection(2).XValues = "='INPUT SHEET'!R13C1:R23C1"
ActiveChart.SeriesCollection(3).XValues = "='INPUT SHEET'!R13C1:R23C1"
End Sub

p45cal
02-18-2013, 08:56 AM
somewhere earlier in the macro:With Sheets("INPUT SHEET")
LR = Cells(.Rows.Count, 1).End(xlUp).Row
End Withand later the likes of:
ActiveChart.SeriesCollection(1).XValues = "='INPUT SHEET'!R13C1:R" & LR & "C1"

banane78
02-19-2013, 08:15 AM
Thank you very much.
Unfortunately it does not work ..can you please integrate the code pieces..I am pretty sure I am doing something wrong while trying to put all together :)

p45cal
02-19-2013, 09:37 AM
Private Sub CommandButton1_Click()
With Sheets("INPUT SHEET")
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
Sheets("CHART-Output").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='INPUT SHEET'!R13C1:R" & LR & "C1"
ActiveChart.SeriesCollection(2).XValues = "='INPUT SHEET'!R13C1:R" & LR & "C1"
ActiveChart.SeriesCollection(3).XValues = "='INPUT SHEET'!R13C1:R" & LR & "C1"
End Sub