ExcelマクロVBA技術解説
Offset、Resizeを使いこなそう

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2019-10-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は、構文も説明も非常によく似ています。
ヘルプの説明だけでは、わかりづらいので、実際の表で説明します。

Ossetと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 ・・・新着記事一覧を見る

VBAにおける変数のメモリアドレスについて|VBA技術解説(11月8日)
空文字列の扱い方と処理速度について(""とvbNullString)|VBA技術解説(1月7日)
Errオブジェクトとユーザー定義エラー|VBA入門(11月5日)
シングルクォートの削除とコピー(PrefixCharacter)|VBA技術解説(11月4日)
ユーザー定義型の制限とクラスとの使い分け|VBA技術解説(11月3日)
クリップボードに2次元配列を作成してシートに貼り付ける|VBA技術解説(11月1日)
VBAクラスを使ったイベント作成(Event,RaiseEvent,WithEvents)|VBA技術解説(10月31日)
VBAクラスのAttributeについて(既定メンバーとFor Each)|VBA技術解説(10月19日)
VBAの用語について:ステートメントとは|VBA技術解説(10月16日)
VBAのマルチステートメント(複数のステートメントを同じ行に)|VBA技術解説(10月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • Offset、Resizeを使いこなそう

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


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




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