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

質問

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

系列：行
データ範囲：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

回答No.5

xls88 · ベストアンサー

＞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

お礼

丁寧にありがとうございます。
記載していただいたコード読み解いて、頑張ります。

補足

Xのセル範囲は１つのグラフの中のすべての系列で同じセル範囲になっています。
B45は数式で参照表記です。転記とややこしく記載してしまってすいません。
B45が参照表記ですが、問題ないでしょうか？

C45は=IF(C35="","",C$27-C35)で合っています。すいません。
見えていないグラフとは、以前何かで作ったであろうグラフがとても小さく縮小されており、さらにグラフの下に隠れて存在していました。そのグラフがインデックス１のグラフでした。

回答No.7

xls88

＞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

お礼

こちらの仕事に手をつけれずお礼が遅くなりました。
提示していただいたVBAを参考にやってみたいと思います。
ありがとうございました。

回答No.6

xls88

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

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

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

回答No.4

xls88

≫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式も教えてください。

補足

=SERIES(グラフ!$B$45,グラフ!$C$26:$J$26,グラフ!$C$45:$J$45,8)

B45　　　他のセルからの転記
C26:J26　1～8までの単純な数値
C45:J45　数式。C35は=IF(C35="","",C$27-C35)

グラフのインデックス番号を調べてみましたら、見ていたグラフはインデックス番号1ではありませんでした。
またインデックス番号も飛び飛びになっています。（見えてるグラフでは）
おそらく見えていないグラフがインデックス番号1になっており、REFになったようです。
よく調べておらず申し訳ありません。

回答No.3

xls88

系列名設定セルを手掛かりに工夫してみました。
データが手入力されていることが条件です。
データを数式で計算している場合はもう一工夫必要です。
これで結果が良ければですが、
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

お礼

ありがとうございます。
頑張って読み解こうと思います！

補足

回答していただいたコードを読み解こうとして、マクロをはしらせてみたら
グラフがただの折れ線グラフでないことに気づきました。
グラフがデータポイントを折れ線でつないだ散布図になっておりました。

SeriesCollection(1).Formulaをとってくる時点で#REFになっておりました。
散布図なのが原因なのでしょうか？

回答No.2

WindsorAvenue

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

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

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

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

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

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

お礼

ありがとうございます！

補足

回答を読み、おお！その方法があった！と思い、これならさほど時間がかからずいけるかも！と思い作業を始めましたが、数値で計算をしているところが多々ありました。その為エラーがでるセルが多数でてしまい、そちらの式を書き直すのが時間がかかってしまいそうです・・・。今回は他の方法を模索します。

回答No.1

keithin

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

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

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

お礼

ありがとうございます。
もうちょっと調べてやってみようと思います

補足

ご指摘されたように行全体を計算させてしまっては意味ないですね。そこに気づいていませんでした。ありがとうございます。
すでにグラフが出来ているファイルがたくさんあり、それを一気に変えてしまいのでVBAで、と思っています。

数字が入っていないセルは、他シートから転記させており、IF文で空文字を入れています。

ある１つのグラフは
項目ラベルはB35からB40。データがC53からH４０に入っています。
範囲がJ40までになっています。
次のグラフの範囲はL３５から始まっています。

範囲を変えてこのようなグラフが１シートに１０ほどあります。

こういった場合、１つのグラフに対して１つずつ範囲を指定して、空文字になっているかどうか調べるしかないのでしょうか？
何かもっとスマートな方法があるのでしょうか？