VBA : auto chart area

来源：互联网发布：万象网管数据库密码编辑：程序博客网时间：2024/05/01 01:26

Sub Auto_Open()

    Dim sheetCount As Integer
    Dim BaseRowNum As Integer
    Dim AvailableRowNum As Integer
    Dim i As Integer
    sheetCount = Application.Sheets.Count
    BaseRowNum = 23

    For i = 1 To sheetCount

        If Sheets(i).ChartObjects.Count >= 1 Then
        Sheets(i).ChartObjects(1).Delete
        End If

        Set mychart = Sheets(i).ChartObjects.Add(Left:=10, Width:=400, Top:=100, Height:=200)
        With mychart.Chart
        .ChartType = xlLine
        .HasLegend = False
        End With

        AvailableRowNum = Application.WorksheetFunction.CountA(Sheets(i).Range("$B$" & BaseRowNum & ":$B$65535")) + BaseRowNum - 1
        Rem Application.Sheets(i).ChartObjects(1).Chart.SetSourceData Source:=Sheets(i).Range("$B$" & BaseRowNum & ":$C$" & AvailableRowNum), PlotBy:=xlColumns

        mychart.Chart.SeriesCollection.NewSeries
        mychart.Chart.SeriesCollection(1).Values = Application.Sheets(i).Range("C" & (BaseRowNum + 1) & ":C" & AvailableRowNum)
        mychart.Chart.SeriesCollection(1).XValues = Application.Sheets(i).Range("B" & (BaseRowNum + 1) & ":B" & AvailableRowNum)

    Next i
End Sub