Excel VBA > Excel上級:マクロ > マクロの記録だけで範囲を可変にする方法
このエントリーをはてなブックマークに追加

マクロの記録だけで範囲を可変にする方法

セル範囲を固定してしまう!?

マクロの記録でよくある事例として表を選択したときに、その範囲が固定となってしまい、行数(通常はデータ)が増えたときにマクロの記録時に指定した範囲を超えてしまい、あふれたデータが集計等の対象になっていない等があります。
ネットの質問掲示板でしばしば見かける「最終行を取得」とか「末尾行を取得」などがこれに当てはまります。
これはマクロの記録時に、文字通りマウスを使ってググッと範囲を指定してしまうことにより起こる問題です。

ショートカットキーを活用する

ショートカットーキーについては別途ページで仕組みを解説しているので、ここではそうならない、つまり「マクロの記録だけで範囲を可変」にする方法を説明したいと思います。
「可変」と言うのは意味としては「固定」の逆です。今回の内容に沿って言えば、「マクロ実行時に範囲を自動的にその都度指定しなおす」イメージです。つまり範囲をマクロ側が勝手に指定するところがキモです。
実はこれ、非常に簡単です。
例として下記の表の範囲だけを選択するマクロを記録してみたいと思います。

上記の表の範囲だけを選択する

そして以下はマウスを使ってセル範囲を取得したときのマクロの記録の内容(VBAコード)です。
■セル範囲をマウスで選択

Sub セル範囲をマウスで選択()
'
‘ セル範囲をマウスで選択 Macro
'

'
    Range(“A1:C4”).Select
End Sub

このようにコードを見ると「Range(“A1:C4”).Select」となっており、いわゆるベタ書きされた状態です。
もしこの方法で範囲を選択した場合でかつ行数が増えた場合には「C4」の部分を「C5」、「C6」、「Cn」として
n行分にその都度、手動で書き換える必要が出てきます。

そこでショートカットの登場です。
先ほども言ったように簡単です。
セルの範囲を選択するときに
「Ctrlキー + Shiftキー + :」をするだけです。「:」はコロンです。

以下はその時のコードです。

■セル範囲をショートカットキーで選択

Sub セル範囲をショートカットキーで選択()
'
‘ セル範囲をショートカットキーで選択 Macro
'

'
    Selection.CurrentRegion.Select
End Sub

となり、上記ではセル範囲の選択部分のコードが「Selection.CurrentRegion.Select」となりました。
これだけで範囲の選択が可変になります。
ポイントはこのショートカットを実行する前に(アクティブ)セルを必ず「表のどこか」に置いておく(クリックする)ことです。
試せばすぐにわかりますが、表の外を選択した状態でこのショートカットキーを実行しても(厳密には違いますが)無反応です。

通常の作業は範囲選択だけのマクロの記録ではないので、このショートカットで選択した後、引き続き通常の作業(操作)をマクロ記録させます。
ちなみに未検証ですが、「Ctrlキー + Shiftキー + 矢印キー」でも同様に可能だと思います。

注意事項

このようにショートカットキーは一般操作として使うときは、「便利、速い」というところに特化されますが、マクロの記録ではマクロの記録時に見た目の動作は一緒でも異なるコードとして記録されます。
そこで範囲選択方法で同じようなショートカットキーに「Ctrlキー + Shiftキー Endキー」があります。
このショートカットキーも実は同じような動作をしますが、以下の点で異なるので注意が必要です。
以下はデータを1行分追加した状態(5行目)です。

これをマクロの記録で範囲を選択します。

■セル範囲をショートカットキーENDで選択

Sub セル範囲をショートカットキーENDで選択()
'
‘ セル範囲をショートカットキーENDで選択 Macro
'

'
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

となりました。例えば、この状態で表の5行目を削除します。
そしてこのマクロ「セル範囲をショートカットキーENDで選択」を実行すると下記の図のようになります。

そうです。削除した行まで含まれた状態で選択されるのです。
このマクロのコードで「SpecialCells(xlLastCell)」の部分がそれに該当します。

一般操作で言うところの「ジャンプ(選択オプション)」-「最後のセル」に該当します。

(一般操作では最後のセルだけが選択されるのに対してマクロでは範囲選択するようになっています)

なお、オートフィルタについては残念ながらセル範囲が指定で記録されるため、こちらは手動で範囲の部分を変更する必要があります(Excel2010)。

ActiveSheet.Range(“$A:$E5″).AutoFilter Field:=10, Criteria1:=”5”

ActiveSheet.Range(“A1″).CurrentRegion.AutoFilter Field:=10, Criteria1:=”5”

ショートカットキーごとにマクロのコードも割り当てられているので注意が必要ですが、逆にこの仕組み(仕様)を知っているととても便利です。

カテゴリ:Excel上級:マクロ