VBA : auto chart area

最新推荐文章于 2024-04-14 12:19:10 发布

icemedia

最新推荐文章于 2024-04-14 12:19:10 发布

阅读量490

点赞数

分类专栏： OTHERS 文章标签： vba integer c

本文链接：https://blog.csdn.net/icemedia/article/details/5876200

版权

OTHERS 专栏收录该内容

6 篇文章

订阅专栏

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