Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Extend Excel chart datarange (Read 3073 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Extend Excel chart datarange
02.07.10 at 10:25:05
Extend Excel chart datarange
The following macro can be used to extend the Excel chart range. The setDataRange can be used to set a new range but it seems impossible to retrieve the datarange. Therefore the code inspects the series and alters each series.
Public Sub chart_set_last_row()
    Dim cho As ChartObject
    Dim ser As Series
    Dim lngLastRow As Long
    Dim vntArr As Variant
    'Ask which row to set as last row'
    lngLastRow = Val(InputBox("Last row", "Set last row", 0))
    For Each cho In Selection
       For Each ser In cho.Chart.SeriesCollection
           vntArr = Split(ser.Formula, ",")
           If Not vntArr(1) = "" Then vntArr(1) = Left(vntArr(1), InStrRev(vntArr(1), "$")) & CStr(lngLastRow)
           vntArr(2) = Left(vntArr(2), InStrRev(vntArr(2), "$")) & CStr(lngLastRow)
           ser.Formula = vntArr(0) & "," & vntArr(1) & "," & vntArr(2) & "," & vntArr(3)
       Next ser
       cho.Chart.Axes(xlCategory).MinimumScaleIsAuto = True
       cho.Chart.Axes(xlCategory).MaximumScaleIsAuto = True
    Next cho
End Sub
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1