如何用VBA实现CSV动态数据的自动XY散点图生成（循环检测空白列）

Here's a revised VBA macro that automatically creates your scatter chart with all Y-axis series starting from column E, stopping at the first blank column header. This avoids using Select/Active for reliability and skips the problematic INDIRECT function.

Full Macro Code #

Sub CreateDynamicScatterChart()
    Dim wsData As Worksheet
    Dim wsGraph As Worksheet
    Dim chtObj As ChartObject
    Dim currentCol As Long
    
    ' Set references to your data and graph worksheets
    Set wsData = ThisWorkbook.Worksheets("data")
    Set wsGraph = ThisWorkbook.Worksheets("graph")
    
    ' Clear existing charts on the graph sheet to prevent duplicates
    wsGraph.ChartObjects.Delete
    
    ' Create a new XY Scatter Smooth No Markers chart on the graph sheet
    Set chtObj = wsGraph.ChartObjects.Add( _
        Left:=10, Top:=10, Width:=600, Height:=400)
    chtObj.Chart.ChartType = xlXYScatterSmoothNoMarkers
    
    ' Start looping from column E (column index 5)
    currentCol = 5
    
    ' Loop until we hit the first blank column header
    Do While wsData.Cells(1, currentCol).Value <> ""
        With chtObj.Chart.SeriesCollection.NewSeries
            ' Set series name to the column header (matches original macro's reference style)
            .Name = "=" & wsData.Name & "!" & wsData.Cells(1, currentCol).Address
            ' X values always come from column D
            .XValues = "=" & wsData.Name & "!" & wsData.Columns(4).Address
            ' Y values from the current column
            .Values = "=" & wsData.Name & "!" & wsData.Columns(currentCol).Address
        End With
        
        ' Move to the next column
        currentCol = currentCol + 1
    Loop
End Sub

Key Improvements & Explanations #

• No Select/Active: We work directly with worksheet and chart objects, making the macro more stable and less prone to errors if you click elsewhere while it runs.
• Auto-Detect Series: The loop checks column headers starting at E, stopping as soon as it finds a blank header—perfect for variable column counts.
• Avoids INDIRECT: Instead of using volatile functions like INDIRECT, we construct range references directly (e.g., =data!$E$1), which plays nicely with Excel's charting engine.
• Clean Slate: We clear existing charts on the "graph" sheet first so you don't end up with multiple charts stacked on top of each other.

Notes #

• Ensure the "data" and "graph" worksheets exist in your workbook before running the macro.
• If you want to use only the used rows (instead of entire columns) for data, you can modify the code to find the last used row in column D and each Y column. For example:

  Dim lastRow As Long
  lastRow = wsData.Cells(wsData.Rows.Count, 4).End(xlUp).Row
  ' Then adjust the range references to use rows 2 to lastRow instead of entire columns
  

内容的提问来源于stack exchange，提问作者MBSPG