【VBA】ActiveCell.Offset等のオフセットを使う

こんにちは、Ryoです。
ExcelVBAで処理する場合にほぼ必須となってくるのがセル操作ですが、使用頻度の多いCellsやRangeと合わせて使うと有用な「Offset」や「Resize」プロパティがあります。

これはセル（又はセル範囲）を移動する目的に使うものですが、例えば特定のセルを検索～抽出して、そこから右にn個目のセルへ書込みを行うなど、処理に幅が広がります。

動作さえ理解してしまえば非常に簡単で手軽に使えますので、今回はそのOffsetについて書いてみたいと思います。

1.Offsetプロパティとは？
2.Offsetの色々な使い方
3.まとめ

1.Offsetプロパティとは？

冒頭にも書きましたが、セルやセル範囲を移動させるものです。

Rangeオブジェクト.Offset（行シフト位置,列シフト位置）

この様な形になります。

アクティブセルに対してOffsetを使った例を参考に行や列のシフト位置を説明すると
以下の様になります。

やはりExcel自体が左上を基点に始まっていることもあり、右方向や下方向が順送り（1→2→3・・・）となり、上方向や左方向が逆送り（ -1→ -2→ -3・・・）になります。

ちなみにActiveCell.Offset(0,1).Selectのように「.Select」とすることで指定した位置に移動しますが、ActiveCell.Offset(0,1)のみであれば、アクティブセルから一つ右のセルを参照する形になりますので、セル自体はアクティブセルの位置から変わりません。

例えばアクティブセルがB6の場合、変数Aに右に3シフトした位置の値を代入するなら
A = ActiveCell.Offset(0,3)という形で記述します。

Sub Sample1()

 Dim A As Variant

 Cells(6, 2).Select
 A = ActiveCell.Offset(0, 3)
 MsgBox "右に3シフトした位置の「" & A & "」を表示します"

End Sub

これを実行すると、画像で示している「３」を表示します。

これが基本的なOffsetプロパティの使い方です。

2.Offsetの色々な使い方

上ではActiveCellに対する移動を説明していますが、この例での指定は単一セルになります。
処理する上では範囲を指定したいことも当然出てきますので、その時にどう記述するかについて書いていきます。

例として以下の画像に示す表の範囲を指定するサンプルをいくつか紹介します。

◆例1：表全体を範囲指定する

Sub Sample2()

　Cells(4, 2).Select
　Range(ActiveCell, ActiveCell.Offset(7, 4)).Select

End Sub

最初に表の「S/N」（セルB4）を選択し、次にRangeを使って範囲を指定しますが、
「ActiveCell」（表左上）と「ActiveCell.Offset(7, 4)」（表右下）を記述することで
表全体を範囲として選択出来ます。

◆例2：S/N「３」の行全体を範囲指定する

Sub Sample3()

 Cells(4, 2).Select
 Range(ActiveCell.Offset(3, 0), ActiveCell.Offset(3, 4)).Select

End Sub

「S/N」位置からActiveCell.Offset(3, 0)で下に3シフトの位置と、
ActiveCell.Offset(3, 4)で3シフト位置から4列を指定しています。

◆例3:OffsetとResizeプロパティを使って範囲を指定する

Offsetプロパティの使い方として、Resizeプロパティと組み合わせる方法もあります。
Resizeプロパティというのはセル範囲のサイズを変更するもので、

オブジェクト.Resize（Rowサイズ,列サイズ）となります。

Sub Sample4()

　Cells(4, 2).Resize(7, 5).Offset(1, 0).Select

End Sub

これは「S/N」のセルに対し、Resizeで行を7、列を5の範囲で指定して表の見出しを除く全範囲を指定する為、「S/N」の位置から下に1シフトしたものです。

この通り、見出しを除く全範囲が選択されていますね。
似たようなプロパティでCurrentRegionがありますが、これは「指定したセルを含む範囲」になってしまう為、用途によってはResizeとOffsetを使う方が良い場合もあります。

使う機会が多いのは上記の3例に類似した形かと思いますので、後は指定範囲等を変更しながら動作させてみて感覚を掴めれば簡単に使いこなせます。

3.まとめ

セル操作でOffsetも合わせて使えると、例えば基準とするセルをFindやFor Eachなどで検索して、関連する場所にOffsetを使ってデータを書き込んだり出来ますから幅も広がりますし記述もだいぶ楽になりますので、お薦めです。慣れればとても簡単ですので、機会あればぜひ使ってみてくださいね。

以上、ActiveCell.Offsetなどのオフセットプロパティを使う内容についてでした！
今回の記事が何かの参考になれば幸いです。

Ryo