|
|
| Excel VBA Macro ・ 範囲選択 ・ FAQ |
|
アクティブセルが変わったら相対的に選択範囲を変えるには？
|
|
|
|
Question 34.1 |
|
 |
 |
|
|
はじめまして。ネット上にこのようなＨＰがあって助かりました。私はリサーチ会社でアルバイトをしている２７才の男性です。今ちょっとしたマクロを作っているのですが、そのことで少し指導をしていただけたら幸いです。
内容としてはシート上の数値を元にして複数のセルを選択したいと思っています。
（例）
横：4、段数：4、間隔：1行飛ばし、アクティブセル：A1の場合
Range("A1:D1,A3:D3,A5:D5,A7:D7").Select
横：4、段数：4、間隔：1行飛ばし、アクティブセル：B2の場合
Range("B2:E2,B4:E4,B6:E6,B8:E8").Select
というように、アクティブセルが変わったら相対的に選択範囲を変えるというものです。
時間がありました一緒に考えて下さい。よろしくお願いします。
|
|
Answer |
Copyright (C) 2000.11.10 永井善王 |
|
|
回答に当ってちょっと困った点があります。それは、シート上の数値を元にして範囲選択したいのか、それとも、アクティブセルが変わったらアクティブセルのアドレスを基準にして範囲選択すればよいのか、どちらでしょうか？
質問文中の「例」を図にしてみます。
|
| アクティブセル：A1の場合 |
アクティブセル：B2の場合 |
 |
 |
|
ここでは上図のとおり、「アクティブセルのアドレスを基準にして範囲選択する」と解釈して進めます。
この課題をクリアするためのポイントが、5つあります。
第1： 複数のセル範囲を同時に選択する方法
第2： アクティブセルのアドレス、つまり、行列番号を取得する方法
第3： アクティブセルの移動方向と、移動した行・列数を取得する方法
第4： 選択範囲をずらす方法
第5： アクティブセルが変わったらマクロを実行する方法
これらのそれぞれの書き方については、この「すぐマク」のホームページの［Macro］のページや［FAQ］のページにパラパラと掲載されているので、いくらかは見られたことと思います。
あなたが指導を望まれている点は、それらをどう組み合わせれば良いのか、かと思います。関係部分をビックアップしながら、考えてみましょう。
1. 複数のセル範囲を同時に選択するマクロ
・・［Macro］−［範囲選択・基本型］のページの「複数のセルとセル範囲を一度に選択」より・・
'-----------------------------------------------------------------------------------------
Private Sub 複数のセル範囲を一度に選択する()
Range("E10:E16,I10:I16,G12:G14,F10:H10,F16:H16").Select '※1、2、3
End Sub
'=========================================================================================
当然ですが、質問にある Range("A1:D1,A3:D3,A5:D5,A7:D7").Select と同様の書き方です。
2. アクティブセルのアドレス、つまり、行列番号を取得するマクロ
・・［Macro］−［範囲選択・基本型］のページの「アクティブセル」より・・
'=========================================================================================
Sub アクティブセルを調べる()
Worksheets("SSS").Activate 'ワークシートをアクティブにする ※１
現在位置列 = ActiveCell.Column 'アクティブセルの列番号
現在位置行 = ActiveCell.Row 'アクティブセルの行番号
アドレス = ActiveCell.Address 'アクティブセルのアドレス
値 = ActiveCell.Value 'アクティブセルの値
End Sub
'-----------------------------------------------------------------------------------------
「アクティブセルの列番号」を取得するマクロは 3行目に、「行番号」の取得は 4行目にあります。
このサブプロシージャーをコピーして、標準モジュールに貼り付けてから実行してみるとわかりますが、変数「現在位置列」には列番号がアルファベットではなくて数字で取得されます。変数「現在位置行」に取得される行番号はもともと数字ですから、アクティブセルが移動した行数と列数を求めるために適しています。
3. アクティブセルの移動方向と、移動した行・列数を取得する方法
アクティブセルが変わる前と後の「現在位置列」と「現在位置行」を取得しておいて、その値を差し引きすれば、上下左右どこにどれだけ変わったのかを掴めます。
たとえば、A列からB列へ移動した場合は、2−1 で 1 となり、1列ずれたことになります。
逆方向に移動した場合は、1−2 で -1 となり、同じく 1列ずれたことになります。
ずれた方向としては、差引した値がプラスならば右方向、マイナスならば左方向です。
言うまでもなく、2行目の '2' から、1行目の '1' を差し引けば、1行下へずれたことになります。
まとめますと、質問文の「アクティブセルが A1 から B2 に変わった」ということは、「アクティブセルが右方向へ1列、かつ下方向へ1行移動した」と言い換えれます。
小難しいことを言われると返ってわからなくなると言わずに、とりあえず先へ進みましょう。
4. 選択範囲をずらすマクロ
・・［Macro］−［範囲選択・基本型］のページの「選択範囲をオフセットする（ずらす）」より・・
'=========================================================================================
Sub 選択範囲をオフセットする()
Worksheets("SSS").Activate 'ワークシートをアクティブにする ※１
Range("A1:D1,A3:D3,A5:D5,A7:D7").Select '複数のセル範囲を一度に選択する ※２
Selection.Offset(1, 0).Select '1行下へ移動する ※３
Selection.Offset(0, 1).Select '1列右へ移動する ※３
Selection.Offset(-1, 0).Select '1行上へ移動する ※３
Selection.Offset(0, -1).Select '1列左へ移動する ※３
End Sub
'=========================================================================================
このマクロは「すぐマク」のホームページに未掲載でしたので、この機会に掲載しておきました。このマクロは、3行目でセル範囲を選択して、その範囲を 4〜7行目でずらしています。ずらす操作は Offsetプロパティを利用しています。
( )の中の数字が (行数, 列数) であることとマイナス符号の意味を、もう、理解できますネ。
5. アクティブセルが変わったらマクロを実行する方法
セル選択は、ユーザーがマウスかキーボードを操作して行うと、思って良いですね。
ワークシートで選択範囲を変更したときに発生するイベントとして、SelectionChange があります。このイベントは、引数「Target」に変更後のセルアドレスが格納されるので、容易にオフセット値を求めることが可能になります。
オフセット値は本来、「アクティブセルが移動する前と後のセルアドレスの差で求められる」と、上記の 3. で解説しましたが次のマクロでは、移動後のセルアドレスと A1セルとの差で求めています。
このマクロは標準モジュールではなくて、ワークシートのコード画面に記述します。
'-----------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
行オフ = Target.Row - 1 '1行目から選択されたセルまでの行オフセット値
列オフ = Target.Column - 1 '1列目 〃 列 〃
選択範囲をオフセットする 'サブプロシージャーを実行する
End Sub
'-----------------------------------------------------------------------------------------
さあ、いよいよ大詰めです。
上記のマクロの 4行目に別のサブプロシージーを実行するように書いてあります。このマクロを組んでみましょう
まず、アクティブセルが A1 の場合に範囲選択する Range("A1:D1,A3:D3,A5:D5,A7:D7").Select
を書きます。
次に、前記 4. の「選択範囲をずらすマクロ」を参考にして、オフセット値を変数で指定するように修正して書きます。
サブプロシージャーとしては、これで出来上がりのように思われますが、意外な問題があります。それは、イベントが発生するのは、ユーザーが操作した場合だけではなく、マクロで範囲選択した場合でも発生します。つまり、Range("A1:D1,A3:D3,A5:D5,A7:D7").Select が実行されると再び、Worksheet_SelectionChange のイベントプロシージャーが実行されてしまいます。
その問題を解決するために、一時的にイベントを無効にするコードを追加します。仕上げると次のとおりになります。
'-----------------------------------------------------------------------------------------
Private Sub 選択範囲をオフセットする()
Application.EnableEvents = False 'イベントを無効にする
Range("A1:D1,A3:D3,A5:D5,A7:D7").Select '複数のセル範囲を一度に選択する
Selection.Offset(行オフ, 列オフ).Select '選択範囲をオフセットする
Application.EnableEvents = True 'イベントを有効にする
End Sub
'=========================================================================================
ほかにも一寸した注意点がありますが、わからない場合はサンプルブックをダウンロードしてから、見てください。
サンプルブックのダウンロードは ここをクリック （YNxv964_Change.xls 52KB）
※ 一旦、ブックをハードディスクに保存し、開き直してから実行してください。
|
|
|
|
| |
|
|
