基準セルから移動・範囲拡張！Excel VBAのOffsetとResizeプロパティ

Excel VBAでオフセットの使い方をお探しですね。

広告

基準セルから移動・範囲拡張！Excel VBAのOffsetとResizeプロパティ

Excel VBAでセル範囲を操作していると、「基準セルから1行下を参照したい」「表のデータ件数に合わせて範囲を広げたい」「見出し行だけ除外したい」といった場面がよく出てきます。

そんなときに活躍するのが、RangeオブジェクトのOffsetプロパティとResizeプロパティです。

どちらもセル範囲を扱うための基本的な機能なんですが、役割を混同してしまうと、意図しないセルを取得してしまうことがあります。

この記事では、初心者の方でも実務で使えるように、この2つの違い、書き方、組み合わせ方、そして注意点まで、わかりやすく整理して解説していきます。

Offsetプロパティは「基準セルから移動」するための機能

Offsetプロパティは、指定したセルやセル範囲を基準にして、「何行・何列ずらした位置」のセルを取得するためのプロパティです。

基本の書き方は `Range.Offset(RowOffset, ColumnOffset)` で、第1引数に行方向、第2引数に列方向の移動量を指定します。

たとえば `Range(“A1”).Offset(1, 0)` と書くと、A1セルの1行下、つまりA2セルを取得できます。

`Range(“A1”).Offset(0, 2)` なら、同じ行の2列右にあるC1セルを表します。

行方向は、下へ移動するときは正の数、上へ移動するときは負の数を指定します。

列方向も同じで、右へ移動するときは正の数、左へ移動するときは負の数です。

つまり、`Offset(1, 1)` は右下、`Offset(-1, -1)` は左上への移動になるわけですね。

どちらか一方だけを動かしたいときは、もう一方に0を指定します。

列方向だけを指定したい場合は `Range(“A1”).Offset(, 1)` のように第1引数を省略することもできますが、初心者のうちは `Offset(0, 1)` とはっきり書いたほうが読み間違いを防ぎやすいです。

ここで大事なポイントがあります。

Offsetは「移動」するだけで、範囲の大きさは変えないんです。

たとえば `Range(“A1:C3”).Offset(1, 0)` と書くと、A1:C3と同じ3行3列の大きさのまま、1行下へ移動したA2:C4を取得します。

単一セルだけでなく複数セルの範囲にも使えるので、表全体を1行下にずらした範囲を参照したい場合にも便利です。

ただし注意点として、ワークシートの外へはみ出すような指定をすると実行時エラーになります。

たとえば、先頭行で `Offset(-1, 0)` を使うようなコードには気をつけましょう。

“`vba
Sub Offsetの基本()
Range(“A1”).Offset(1, 0).Value = “A1の1行下”
Range(“A1”).Offset(0, 1).Value = “A1の1列右”
Range(“B2:D4”).Offset(2, 1).Interior.Color = vbYellow
End Sub
“`

Resizeプロパティは「範囲の大きさを指定し直す」ための機能

Resizeプロパティは、指定したセルやセル範囲を基準にして、行数と列数を指定し直すためのプロパティです。

基本の書き方は `Range.Resize(RowSize, ColumnSize)` で、第1引数に取得したい行数、第2引数に取得したい列数を指定します。

たとえば `Range(“A1”).Resize(5, 3)` と書くと、A1セルを左上の起点として、5行3列の範囲、つまりA1:C5を取得します。

表の先頭セルが決まっていて、そこからデータ範囲をまとめて処理したいときに、とても使いやすいプロパティです。

Resizeで特に間違えやすいのが、引数の意味です。

これは「増やす数」ではなく、「最終的に取得したい行数・列数」なんです。

たとえば `Range(“A1:C5”).Resize(1)` と書くと、「1行増やす」のではなく、「1行の範囲に作り直す」という意味になります。

その結果、A1:C1のように先頭行だけの範囲になってしまいます。

既存の範囲を1行分広げたい場合は、元の行数に1を足して指定する必要があります。

ここをしっかり理解しておくと、Resizeによる範囲拡張や縮小の失敗をぐっと減らせます。

たとえば、A3:E7の表を下方向に1行広げたい場合は、現在の行数を `Rows.Count` で取得して、それに1を足してResizeに渡します。

列数を変えない場合、第2引数は省略できます。

同じように、右方向へ1列広げたい場合は、列数を `Columns.Count` で取得して、それに1を足して第2引数に指定します。

Resizeは左上のセルを基準にして右下方向へ範囲を作り直す性質があるので、通常の表データを下や右へ拡張する処理と相性がいいんです。

“`vba
Sub Resizeで範囲を広げる()
Dim rng As Range
Set rng = Range(“A3:E7”)

‘ 下方向に1行広げる：A3:E8
rng.Resize(rng.Rows.Count + 1).Select

‘ 右方向に1列広げる：A3:F7
rng.Resize(rng.Rows.Count, rng.Columns.Count + 1).Select
End Sub
“`

Resizeを使えば、データ件数に応じた範囲指定も簡潔に書けます。

たとえば、A1を起点に10件分のデータを取得するなら `Range(“A1”).Resize(10, 1)` と書けます。

これは `Range(“A1:A10”)` と同じ意味ですが、件数が変数で決まる場合にはResizeのほうが柔軟に対応できます。

固定のアドレス文字列を組み立てるよりも、基準セルと行数・列数を分けて考えられるので、後からコードを読んだときにも意図を追いやすくなります。

OffsetとResizeを組み合わせると見出し除外や範囲調整が簡単になる

OffsetとResizeは単独でも便利ですが、実務では組み合わせて使う場面がとても多いです。

代表的な例が、表全体から見出し行を除外してデータ部分だけを取得する処理です。

たとえば、A1:D10が見出しを含む表全体だとします。

このうちA2:D10だけを処理したい場合、まずOffsetで範囲全体を1行下へ移動して、その後Resizeで行数を1行分減らします。

これで、見出し行を除いたデータ部分だけを正確に取得できます。

“`vba
Sub 見出し行を除外する()
Dim tableRange As Range
Dim dataRange As Range

Set tableRange = Range(“A1:D10”)
Set dataRange = tableRange.Offset(1, 0).Resize(tableRange.Rows.Count – 1, tableRange.Columns.Count)

dataRange.Interior.Color = vbYellow
End Sub
“`

このコードの考え方は、「先頭位置を下へずらす」と「ずらした分だけ末尾を短くする」の2段階です。

Offsetだけだと、A2:D11のように範囲の大きさが変わらず、元の表より1行下にはみ出してしまいます。

一方、Resizeだけでは左上の基準セルが変わらないので、見出し行を除外できません。

そのため、上側や左側を削りたいときは、OffsetとResizeをセットで考えるのが基本なんです。

左端の列を除外したい場合も同じ考え方です。

たとえばA1:D10からA列を除いたB1:D10を取得したいなら、Offsetで1列右へ移動して、Resizeで列数を1列分減らします。

見出し行と左端列を同時に除外したい場合は、`Offset(1, 1)` で開始位置を右下へ移動して、行数と列数をそれぞれ1ずつ減らします。

表のID列や見出し行を除いて、数値データ部分だけを集計・書式設定・配列化したいときに役立つ書き方です。

“`vba
Sub 見出し行と左端列を除外する()
Dim tableRange As Range
Dim bodyRange As Range

Set tableRange = Range(“A1:D10”)
Set bodyRange = tableRange.Offset(1, 1).Resize(tableRange.Rows.Count – 1, tableRange.Columns.Count – 1)

bodyRange.Select
End Sub
“`

CurrentRegionと組み合わせる方法も、実務ではよく使われます。

CurrentRegionは、空白行や空白列で囲まれたひとまとまりの表範囲を取得するプロパティです。

たとえば `Range(“A1”).CurrentRegion` で表全体を取得して、そこからOffsetとResizeで見出しを除外すれば、行数が増減する表にも対応できます。

ただし、表の途中に完全な空白行や空白列があると、CurrentRegionの範囲が途中で切れてしまうので、データ構造に合わせて使う必要があります。

実務で使うときの注意点と読みやすいコードの書き方

OffsetとResizeを実務で使う際は、まず「移動したいのか」「大きさを変えたいのか」を分けて考えることが大切です。

基準セルから別の位置を参照したいならOffset、同じ起点から範囲の行数・列数を変えたいならResizeです。

上や左を削る場合は起点そのものを変える必要があるので、Offsetで開始位置を移動してからResizeで範囲サイズを調整します。

この役割分担を意識すると、複雑な範囲指定でもコードの意味が崩れにくくなります。

また、Resizeの引数には0以下の値を指定できません。

たとえば、1行しかない範囲に対して `Rows.Count – 1` を指定すると0になって、実行時エラーの原因になります。

見出し行を除外する処理を書く場合は、表にデータ行が存在するかを確認してから処理するのが安全です。

Offsetについても、1行目から上方向へ移動したり、A列から左方向へ移動したりすると、ワークシートの範囲外になるためエラーになります。

自動処理のマクロでは、対象範囲が想定どおりかを事前に確認する習慣が重要です。

“`vba
Sub 安全に見出しを除外する()
Dim tableRange As Range
Dim dataRange As Range

Set tableRange = Range(“A1”).CurrentRegion

If tableRange.Rows.Count <= 1 Then MsgBox "データ行がありません。 " Exit Sub End If Set dataRange = tableRange.Offset(1, 0).Resize(tableRange.Rows.Count - 1, tableRange.Columns.Count) dataRange.Copy Destination:=Range("G1") End Sub ``` コードを読みやすくするには、同じRangeを何度も直接書かず、変数に入れてから操作するのがおすすめです。 `Range("A1").CurrentRegion.Offset(1, 0).Resize(...)` のように長くつなげることもできますが、処理が複雑になるほど、どの時点の範囲を基準にしているのか分かりにくくなってしまいます。 `tableRange`、`dataRange`、`targetRange` のように役割が分かる名前を付けると、後から修正するときも安心です。 特に業務マクロでは、動けばいいだけでなく、数か月後に自分や他の人が読んでも理解できることが大切です。 さらに、Rangeを扱うときはシートを明示することも重要です。 `Range("A1")` とだけ書くと、アクティブシートのA1を参照します。 マクロの実行中に別のシートが選択されていると、意図しないシートを操作してしまう可能性があります。 実務では `Worksheets("売上").Range("A1")` のようにワークシートを指定して、その範囲に対してOffsetやResizeを使うほうが安全です。 SelectやActivateを多用せず、Rangeオブジェクトを直接操作する書き方にすると、処理速度と安定性の面でも有利になります。

まとめ

OffsetとResizeプロパティを正しく使えるようになると、Excel VBAのセル範囲操作は一気に柔軟になります。

Offsetは基準からの移動、Resizeは範囲サイズの再指定、そして両者の組み合わせは見出し除外やデータ部分の抽出に役立ちます。

表の行数が変わるたびにアドレスを書き換えるのではなく、基準セル・行数・列数を組み合わせて考えることで、変化に強いマクロを書けるようになります。

まずは小さな表で動作を確認しながら、どの範囲が取得されるのかを理解していくと、実務でも迷わず使えるようになりますよ。

広告