すぐに役立つエクセルVBAマクロ集 FAQ 自動記録した範囲指定のコードを変数指定に

　すぐマク　YNxv9621

自動記録した範囲指定のコードを変数指定に修正するには？

Ｑuestion 64.2　範囲指定　　　　（質問文をY.Nagaiが編集）

いつもお世話になっております。組み方講座を活用させて頂いています。
以前に質問したことがあり、その続きの質問があります。その際に作成して頂いたサンプルは「コンボボックスの選択結果からまとめた情報を縦方向のセルに表示したいが？」になります。

そのサンプルを元にいろいろ改良し、今回は分布グラフを自動作成する機能をつけようと取り組んでいます。
そのため、マクロを自動記録で作成しましたが、グラフデータとして選択したセル範囲が固定されています。
実務では、コンボボックスで選らばれる会社：飛行機名は、そのつど増減します。それに対応したグラフにするにはどうすればよいでしょうか。

私が考えて行った方法は下記のとおりです。
　第1図：「Comparison」シート　　　　　　　　　第2図：「グラフ作成」シート

第1図が比較する対象データで、コンボボックスで選んだ4つの会社分です。
（質問に無関係の行は非表示です）
第2図はグラフ作成用に第1図のデータから編集して、別のシートに作成しました。
具体的には、下記のとおり操作して自動記録しました。
1. 第1図のC53:F53の会社名をコピーして、第2図のA2へ行と列を入れ替えて貼り付け
Sheet1

2. 同 C12:F12の時間をコピーして、第2図のB1へ貼り付け
3. 同 C49:F49の価格をコピーして、一旦、第2図のB2へ貼り付け
4. その価格を階段状にするためC2セルを切り取ってC3へ貼り付け
5. 同様にして D2をD4へ、E2をE5へ
6. グラフデータとして A1:E5セルを選択しグラフを作成

そして、このシートに右図のとおり、分布図のグラフを作成しました。
グラフの元データ範囲は第2図のA1:E5です。

組み方講座291ページの For…Nextステートメントを使えばいいのではないかと思うのですが、具体的にどうすれば良いのか分かりません。
大変、お手数ですが宜しくお願いいたします。

Ａnswer　　

以前ご質問いただいたのは、今夏の終わり頃でしたね。
そのときは、旅行プランの一覧表から、いくつかのコースを選択して、別のワークシートに右図のようにまとめるマクロでした。
（コンボボックスはコース選択に利用）

このワークシートを発展したものが第1図で、そのシートからグラフを描きたいが、コースの増減に伴い列が増減するので、範囲指定をどうすればよいのかご質問ですね。

ポイント1 ：第1図の53行目（会社名）の有効セル範囲を変数で指定してコピーする
左端は C列で良いとして、右端の列番号を取得しなければなりません。いろいろな方法がありますが、デザインされたワークシートですから下記のようにしましょう。

    Worksheets("Comparison").Activate           'シートをアクティブにする
    右端列 = Range("C53").CurrentRegion.Columns.Count + 1 'アクティブセル領域の列数+1 [注1]
    Range(Cells(53, 3), Cells(53, 右端列)).Copy '会社名をコピーする [注2、3]

ポイント2 ：同 12行目（時間）も変数を使って範囲指定しコピーする

    Range(Cells(12, 3), Cells(12, 右端列)).Copy '時間をコピーする

ポイント3 ：同 49行目（価格）もポイント2 と同様
ポイント4 ：貼り付けた価格を階段状にするため変数を変化させながら切り取り・貼り付けする
ここで、For…Nextステートメントを利用します。
1回目のループで C2セルをコピーしてC3セルへ貼り付けし、2回目は D2をD4へ、3回目は E2をE5へとなりますが、何回繰り返すかは場合により変わっても良いようにしておきます。

    For コピー列 = 3 To 右端列
        Range(Cells(2, コピー列), Cells(2, コピー列)).Cut
        貼付行 = コピー列
        Range(Cells(貼付行, コピー列), Cells(貼付行, コピー列)).Select
        ActiveSheet.Paste
    Next

ポイント5 ：グラフデータの範囲を変数で指定して選択する
変数「右端列」と、上記のループで得られた下端行を示す変数「貼付行」の値を使って、下記のとおり選択します。

    Range(Cells(1, 1), Cells(貼付行, 右端列)).Select

これより後の処理、つまり、グラフを描画するためのコードは、あなたが自動記録されたままで使えます。
また、ポイント1～3の解説は貼り付けるためのコードを省略していますが、自動記録されたコードのままで結構です。
さあ、がんばって修正しましょう。

・修正したマクロは こちら
[注1]　「アクティブセル領域の行列数を調べる」参照
[注2]　「Excel VBAマクロ組み方講座」本の350ページ「セル範囲をR1C1形式の変数で指定して選択する」参照
[注3]　同 314ページ「自動記録マクロの特徴」参照
関連ページ　コンボボックスの選択結果からまとめた情報を縦方向のセルに表示したいが？

http://www.geocities.jp/happy_ngi/