VBA技術解説
Offset、Resizeを使いこなそう

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2013年5月以前 最終更新日:2020-08-26

Offset、Resizeを使いこなそう


OffsetとResizeは、エクセルVBAで非常に便利であり、ぜひ使いこなしてもらいたい機能です。
Offsetはセル範囲の位置をずらし、Resizeはセル範囲のサイズを変更します。
どちらも、RangeとCellsだけで記述することは可能ですが、
OffsetとResizeを使う事で、簡潔にVBAを記述することができるようになります。


OffsetもResizも、Rangeオブジェクトのプロパティで、Rangeオブジェクトを返します。
まずは、それぞれの構文の確認から。

OffsetプロパティとResizeプロパティの構文

以下、ヘルプより

Offsetプロパティ

指定された範囲からのオフセットの範囲を表すRangeオブジェクトを返します。

Offset(RowOffset, ColumnOffset)

RowOffset
オプション バリアント型 (Variant) オフセットする範囲の行数 (正、負、または 0) を指定します。
正の値は下方向、負の値は上方向のオフセットを表します。既定値は 0 です。
ColumnOffset
オプション バリアント型 (Variant) オフセットする範囲の列数 (正、負、または 0) を指定します。
正の値は右方向、負の値は左方向のオフセットを表します。既定値は 0 です。

Resizeプロパティ

指定された範囲のサイズを変更します。サイズが変更されたセル範囲(Rangeオブジェクト) を返します。

Resize(RowSize, ColumnSize)

RowSize
オプション バリアント型 (Variant) 新しい範囲の行数を指定します。
この引数を省略すると、新しい行数は変更する前と同じ行数になります。
ColumnSize
オプション バリアント型 (Variant) 新しい範囲の列数を指定します。
この引数を省略すると、新しい列数は変更する前と同じ列数になります。
OffsetとResizeは、構文も説明も非常によく似ています。
ヘルプの説明だけでは、わかりづらいので、実際の表で説明します。

OffsetとResizeの基本

以下の表で説明します。

マクロ VBA Offset Resize

Offset

Range("A1").Offset(1, 1).Select

A1セルから、1行下1列列右にオフセット(移動)したセルになりますので、
B2セルが選択されます。

マイナス値を指定すれば、上のセルや左のセルに移動できます。

Range("B4").Offset(-2, -1).Select

2行上1列左に移動しますので、
A2セルが選択されます。

セル範囲の大きさ(行数・列数)は、元の大きさのまま移動された結果になります。

Range("A2:B3").Offset(1, 0).Select

2行上0列(列は移動しない)ので、
A3:B4が選択されます。
0は省略できますので、
Offset(1)
これだけでも良いです。

Resize

Range("A1").Resize(2, 2).Select

A1セルを起点に、2行2列の範囲になりますので、
A1~B2セルが選択されます。

Resize(1, 1)は、元のセル範囲の左上のセルになります。

Offsetの注意点

A1セルのOffset(1)がA2セルになるとは限りません。

MsgBox Range("A1").Offset(1).Address

この結果は、"$A$2"になる場合と、違う結果になる場合があります。
A1セル~A3セルがセル結合されている場合、
上記VBAの結果は、"$A$4"となります。
つまり、
Offsetプロパティでは、セル結合されてる範囲は一つとして数えられます。
従って、Range("A1").Offset(2)これはA5セルになります。

列方向も同様になります。
A1セル~C3セルが結合されている場合、

MsgBox Range("A1").Offset(1).Address 

この結果は、"$D$1"になります。

OffsetとResizeの応用

では、先の表で、
データ部(A2~B11)を全て選択する場合です。
以下では、OffsetとResizeの書き方のバリエーションとして、あえて冗長な記述もしています。
また、他の記述を排除して見やすくするためにシート修飾は省略しています。

Sub sample1()
  Dim RowMax As Long
  RowMax = Cells(Rows.Count, 1).End(xlUp).Row
  Range(Range("A2"), Range("A1").Offset(RowMax - 1, 1)).Select
End Sub

RowMax = Cells(Rows.Count, 1).End(xlUp).Row
これは、A列の最終行を取得しています。
RowMax = Range("A1").CurrentRegion.Rows.Count
これでも結果は同じになります。
Range("A1").Offset(RowMax - 1, 1)
A1から行数分下、1列右に移動したセルです。
つまり、B列の最終行のセルを参照しています。
そして、Rangeで、A2~B列の最終行のセルの範囲を選択しています。

Sub sample2()
  Dim RowMax As Long
  RowMax = Cells(Rows.Count, 1).End(xlUp).Row
  Range("A2").Resize(RowMax - 1, 2).Select
End Sub

A2セルを起点に、行数と2列に範囲のサイズを変更しています。

OffsetとResizeは、これを使用しないと出来ない、というような事はありません。
常に別の方法が存在していますし、その方が分かり易い場合も多いです。
上記例なら、

Sub sample3()
  Dim RowMax As Long
  RowMax = Cells(Rows.Count, 1).End(xlUp).Row
  Range(Range("A2"), Cells(RowMax, 2)).Select
End Sub

これで出来ますね。
この例の場合なら、OffsetもResizeも不要かもしれません。

しかし、ある程度複雑なマクロを作成している場合で、
起点のセルが、オブジェクト変数に入っていたりすると、
上記のような記述が困難な場合が多く出てきます。
そのような場合に、OffsetとResizeがとても便利な場合が出てきます。

OffsetやResizeでVBAが簡潔になる例

例えば以下のような場合を考えてみましょう。
引数のRangeの右横を選択するプロシージャーです。

Sub sample11(ByRef myRange As Range)
  Cells(myRange.Row, myRange.Column + 1).Select
End Sub

このVBAは、1セルにしか対応していません。
複数セルに対応させるとなると、さらに長い記述が必要になります。
しかし、Offsetを使えば、

Sub sample12(ByRef myRange As Range)
  myRange.Offset(0,1).Select
End Sub

これだけで、複数セルでも正しく動作してくれます。
(ただし、列数については1列限定になります。)
Offsetが理解できてしまえば、とても簡単で理解しやすいVBAになります。

さらに、以下のような場合も考えてみましょう。
引数のRangeの最左端の列だけを選択するプロシージャーです。

Sub sample13(ByRef myRange As Range)
  Range(Cells(myRange.Row, myRange.Column), _
     Cells(myRange.Row + myRange.Rows.Count - 1, myRange.Column)).Select
End Sub

かなり長い記述になってますね。
書き方はいろいろありますが、どのように書いてもそれなりに長い記述になってしまいます。
しかし、Resizeを使えば、

Sub sample14(ByRef myRange As Range)
  myRange.Resize(, 1).Select
End Sub

たったこれだけの記述で済んでしまいます。
Resizeを知らなかったら、どうしようかと悩んでしまいますね。

OffsetとResizeを使わないと、VBAに苦労してしまう事も多々あります。
ぜひとも、OffsetとResizeを覚えて使いこなしてください。



同じテーマ「マクロVBA技術解説」の記事

ローカルウィンドウの使い方
WorksheetFunctionについて
RangeとCellsの深遠
Offset、Resizeを使いこなそう
値渡し(ByVal)、参照渡し(ByRef)について
最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)
ユーザー定義関数の作り方
セルの値について(Value,Value2,Text)
Excelのバージョンを判断して「名前を付けて保存」
空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)
空白セルを正しく判定する方法2


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ