グラフの範囲指定をVBAで可変にしたい

Question

１シートに複数のグラフがあり、値を入れて完成させているファイルがあります。
データ範囲が毎回違うため、いちいちグラフを選択し、範囲を変更しています。

系列：行
データ範囲：A列から～H列までだったり、A列からJ列までだったりします

データがH列までの場合はI列は値が入っていませんが、L列からまた他のグラフのデータ範囲になっているという形です。

データが始まる位置はすべて固定です。

シートにある複数のグラフを一度に変更したいのですが、どのようにしたら良いのでしょうか？
（選択しているグラフのグラフ変更ではなく、選択していないものも変更したい）
以下のようなマクロを仮で組んでみたのですが、うまく動きません。

よろしくお願いします。

Sub グラフ範囲変更()
  Dim lastcolumn As Integer
 lastcolumn = Range("A2").End(xlToRight).Column
  ActiveChart.SetSourceData Source:=Range(Cells(1, 1), Cells(1, lastcolumn))
 End Sub

xls88 · Accepted Answer

＞No.4 この回答への補足
＞=SERIES(グラフ!$B$45,グラフ!$C$26:$J$26,グラフ!$C$45:$J$45,8)
SERIES式をみると
Xのセル範囲とYのセル範囲の行数が系列NO以上に離れています。
各系列のYのセル範囲もそれぞれ離れているのですか？
それとも隣り合っていますか？

各系列のYのセル範囲は隣接しているが、Xのセル範囲とは数行離れているようです。
違いますか？
No.4までの回答は
各系列名セル範囲、Xのセル範囲、各系列のYのセル範囲は隣接して
矩形セル範囲内にあるとして考えています。
従って、思ったような結果は得られていないと思います。

＞B45　　　他のセルからの転記
手打ち入力ですか？それとも数式で参照表示ですか？
手打ち入力という事ですね？

＞C26:J26　1～8までの単純な数値
手打ち入力という事ですね？

＞C45:J45　数式。C35は=IF(C35="","",C$27-C35)
C35はC45の間違いでは？
C45は=IF(C35="","",C$27-C35)

＞おそらく見えていないグラフがインデックス番号1になっており、REFになったようです。
インデックス番号の所為ではないと思います。
「見えていないグラフ」とは？？？です。

新たに提示して頂けた情報から判断して
系列ごとにデータ範囲を設定した方が確実かも知れません。
戻り値が数値のセルは連続している事として
SpecialCellsプロパティで、戻り値が数値になっているセル範囲を特定します。
グラフ指定でChartObjects(1)のインデックスは適宜書き換えてください。
ChartObjects("グラフ 1")というように名前にした方が特定し易いかも知れません。
まだまだ検討の余地はありますが取りあえずサンプルコードを提示してみます。

Dim ser As Series
    Dim fmla As String
    Dim xrng As Range
    Dim yrng As Range
    Dim plt As Long
    
    With ActiveSheet.ChartObjects(1)
        plt = cht.Chart.PlotBy
        If plt <> 0 Then
            For Each ser In cht.Chart.SeriesCollection
                fmla = ser.Formula
                Set xrng = Range(Split(fmla, ",")(1))
                Set yrng = Range(Split(fmla, ",")(2))
                Select Case plt
                    Case 1  'xlRows(データ系列は列)
                        Set yrng = Range(yrng, yrng.End(xlToRight))
                        Set yrng = yrng.SpecialCells(xlCellTypeFormulas, 1)
                    Case 2  'xlColumns(データ系列は行)
                        Set yrng = Range(yrng, yrng.End(xlDown))
                        Set yrng = yrng.SpecialCells(xlCellTypeFormulas, 1)
                End Select
                With ser
                    .XValues = xrng.Resize(yrng.Rows.Count, yrng.Columns.Count)
                    .Values = yrng
                End With
            Next ser
        End If
    End With

xls88 · Answer

＞No.3 この回答への補足
＞SeriesCollection(1).Formulaをとってくる時点で#REFになっておりました。
#REFを探して削除できるかも知れません。

Dim ser As Series
    For Each ser In ActiveSheet.ChartObjects(1).SeriesCollection
        If InStr(ser.Formula, "#REF") > 0 Then
            cht.Delete
            Exit For
        End If
    Next

xls88 · Answer

＞No.5 この回答への補足
＞B45が参照表記ですが、問題ないでしょうか？
問題ありません。

＞見えていないグラフとは、以前何かで作ったであろうグラフがとても小さく縮小されており、
＞さらにグラフの下に隠れて存在していました。そのグラフがインデックス１のグラフでした。
使われていない不要なグラフの残骸なら削除した方が良いと思います。

No.5のコードはデータが増減する場合に対応しています。
系列の増減には未対応です。
再掲になりますが
グラフ指定でインデックスは適宜書き換えて実行してください。
あるいは
ChartObjects("グラフ 1")というように名前で指定してみてください。

xls88 · Answer

≫No.3 この回答への補足
＞グラフがデータポイントを折れ線でつないだ散布図になっておりました。
＞SeriesCollection(1).Formulaをとってくる時点で#REFになっておりました。
グラフでは問題ないのですか？
コードの問題ではないと思います。

No.3のコードではデータの行・列が入れ替わる不具合がありました。
PlotByプロパティを取得、再設定するようにしました。
棒、折れ線、散布図で確認しています。

Dim fmla As String
    Dim nrng As Range
    Dim drng As Range
    Dim plt As Long
    Dim r As Long
    Dim c As Long
    
    With ActiveSheet.ChartObjects(1).Chart
        fmla = .SeriesCollection(1).Formula
        plt = .PlotBy
        If Split(Split(fmla, ",")(0), "(")(1) <> "" Then
            Set nrng = Range(Split(Split(fmla, ",")(0), "(")(1))
            r = Range(nrng, nrng.End(xlDown)).Rows.Count
            c = Range(nrng, nrng.End(xlToRight)).Columns.Count
            Select Case .PlotBy
                Case 1  'xlRows(データ系列は列)
                    r = r + 1
                    Set nrng = nrng.Offset(-1)
                Case 2  'xlColumns(データ系列は行)
                    c = c + 1
                    Set nrng = nrng.Offset(, -1)
            End Select
            Set drng = nrng.Resize(r, c)
            .SetSourceData Source:=drng, PlotBy:=plt
        Else
            MsgBox "系列名が設定されていません"
        End If
    End With

No.3でも書きましたが、データを数式で表示している場合は使えない状況が考えられます。
その場合は、どんな式が入っているか教えてください。
プロットされたマーカー（または線）をクリックすれば数式バーにSERIES式が表示されます。
どれかひとつで良いのでSERIES式も教えてください。

xls88 · Answer

系列名設定セルを手掛かりに工夫してみました。
データが手入力されていることが条件です。
データを数式で計算している場合はもう一工夫必要です。
これで結果が良ければですが、
For文でChartObjectを総当たりするコードを付けくわえてください。

Dim fmla As String
    Dim nrng As Range
    Dim xrng As Range
    Dim drng As Range
    Dim r As Long
    Dim c As Long
    
    With ActiveSheet.ChartObjects(1).Chart
        fmla = .SeriesCollection(1).Formula
        If Split(Split(fmla, ",")(0), "(")(1) <> "" Then
            Set nrng = Range(Split(Split(fmla, ",")(0), "(")(1))
            r = Range(nrng, nrng.End(xlDown)).Rows.Count
            c = Range(nrng, nrng.End(xlToRight)).Columns.Count
            Select Case .PlotBy
                Case 1
                    r = r + 1
                    Set xrng = nrng.Offset(-1)
                Case 2
                    c = c + 1
                    Set xrng = nrng.Offset(, -1)
            End Select
            Set drng = xrng.Resize(r, c)
            .SetSourceData Source:=drng
        Else
            MsgBox "系列名が設定されていません"
        End If
    End With

WindsorAvenue · Answer

間違っていたらゴメンなさい。

グラフは折れ線グラフで、データがない時にゼロまで線がひかれてしまうため、
毎回データ範囲を変更されているとしたら、以下のようにされると線がひかれません。

(1)データが手入力の場合、
　空白ではなく、#N/A　と入力

(2)関数で引張ってくる場合、
　=IF(SINA(VLOOKUP(・・・,"",VLOOKUP(・・・
　といった例えばこんな関数が入っていたら、　""　を　NA()　に変更

そうすると、データ範囲を変えなくても折れ線グラフはデータがある所までしか
表示されませんよ。

#N/Aの見栄えが悪いなら、条件付書式で見えないようにしたらＯＫです。

keithin · Answer

わざわざマクロなど使わず，「名前の定義」を併用してエクセルにグラフの対象範囲を自動計算させてしまうのがよいでしょう。

参考にしてください：
http://www2.odn.ne.jp/excel/waza/graph.html#SEC13
ただし，たとえば２行目の全体を対象範囲で計算させてしまっては，今のマクロの失敗と同じ轍を踏んでしまうので気を付けてください。

また次のような点が説明不足なので，ただマクロを書いて貰っても，また「書いてあるそのまま丸写し」しても上手く出来ないと思います。
●何列が系列名で何列から何列が実際に数字が入ってカタマリにしたい列なのか
●何行目が「項目ラベル」で何行目から何行目の範囲に数字が入っているのか
●数字の入っていないセルは，ホントに空っぽにしているのか，それとも数式で計算させていて数字が表示されていないだけなのか
ご自分で手順を理解して，キチンと自作してみてください。

グラフの範囲指定をVBAで可変にしたい

＞No.4 この回答への補足

この回答への補足

＞No.3 この回答への補足

＞No.5 この回答への補足

≫No.3 この回答への補足

この回答への補足

系列名設定セルを手掛かりに工夫してみました。

この回答への補足

間違っていたらゴメンなさい。

この回答への補足

わざわざマクロなど使わず，「名前の定義」を併用してエクセルにグラフの対象範囲を自動計算させてしまうのがよいでしょう。

この回答への補足

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング