Excel VBAでセルの相対位置を簡単に指定する方法

offsetプロパティについて解説 VBA
2024.08.30

指定したセルから、特定の数値ずらしたセルの内容を取得したい。。。
そんな悩みをお持ちではないですか？

お悩みポイント
悩む人
  • 任意のセルから相対的な位置にあるデータを取得したい
  • 手動でセル位置を計算する手間を省きたい
  • データ範囲を柔軟に操作できるようにしたい

この記事では、VBAの「Offsetプロパティ」を使って、特定のセルから相対的な位置にあるセルや範囲を指定する方法を詳しく解説します。

「Offsetプロパティ」を使用することで、セル範囲の移動やデータの取得が簡単になり、Excel作業の効率を大幅に向上させることができます。

使用例もすべてコピペして実行できるので、ぜひ最後までご覧いただき、実践してみてください。

【　この記事の概要　】

　　よく使う度　　　　3.0　　
　　難しさ　　　　　3.0　　
　　覚えておくと安心度　　　　　3.0　　

この記事でできるようになること

この記事を読むことで、以下のことができるようになります。

この記事を見てできるようになること

VBAの「Offsetプロパティ」とは？

「Offsetプロパティ」は、VBAで指定したセルから相対的な位置にあるセルを参照するために使用されます。

基準となるセルを起点として、行数と列数を指定することで、任意のセルを動的に取得することができます。

このプロパティを使うことで、動的にセル範囲を操作したり、データの追加・削除時にも柔軟に対応することが可能です。

Offsetプロパティはどのような事ができるのか？

VBA Offsetプロパティの基本構文

offsetプロパティの構文
設定解説
  • expression
    • オフセットを計算する基点となるセル（例: “A1″）
  • RowOffset
    • 基準セルからの移動する行数。
      正の値で下方向、負の値で上方向に移動します。
  • ColumnOffset
    • 基準セルからの移動する列数。
      正の値で右方向、負の値で左方向に移動します。

より詳しい情報は、Microsoft公式 – Range.Offset プロパティ (Excel)を参照してみてください。

VBA Offsetプロパティの使い方

次のようなExcelの表を例に、Offsetプロパティを使用してセル範囲を動的に操作する方法を解説します。

offsetプロパティの使い方解説。操作する表

まず、セル「B2」を基点として、その「2行下」で「3列右」にあるセルを選択する例を見てみましょう。

Sub SelectOffsetCell()
    Range("B2").Offset(2, 3).Select
End Sub

処理結果

offsetプロパティの使い方解説。操作解説

このコードを実行すると、セル「B2」から「2行下（行番号+2）」、「3列右（列番号+3）」にある、セル「E4」が自動的に選択されます。

このように、「Offsetプロパティ」を使えば、Excel VBAでの範囲の指定が柔軟にできるため、さまざまな作業で手間を大幅に削減できます。

VBA Offsetプロパティがよく使われる事例

セルの相対的な位置を柔軟に取得できる「Offset」プロパティは、以下のような場面でよく使用されます。
※実際の使用例は、「使用例のご紹介」の項で解説しています。

よく使われる事例
  • 新しいデータの挿入や更新
    • Offsetを使用して、既存のデータから一定の位置に新しいデータを挿入したり、更新を行うことができます。

      たとえば、最終行に新しいデータを追加する際に便利です。
  • データのループ処理
    • Offsetを用いて、基準セルを変化させながら特定の処理を繰り返すループを作成することができます。
      これにより、行や列を一つずつ操作する必要がなくなり、VBAコードがシンプルになります。
    • 特定の条件を満たすデータを探し出すために、ループとOffsetを使用して基準セルを順に操作しながらデータを取得することができます。
  • テーブルのデータを取得・削除する
    • CurrentRegionとOffsetを組み合わせることで、テーブル（表）のヘッダーは残しつつ、データだけを取得や削除することが可能です。

      例えば、2次元配列でテーブルのヘッダーを取得せず、データ部分のみを操作したい場合、Offsetを使うことでヘッダーを除いた範囲を動的に選択できます。
注意ポイント

Offsetを使う際の注意ポイント

「Offsetプロパティ」は非常に便利ですが、使用する際にはいくつかの注意点があります。

無効な範囲への移動

Offsetで指定したセルが、シートの外（たとえば、負の行数や列数）に出てしまうとエラーが発生します。
したがって、必ず有効な範囲内で使用するようにしてください。
特にループ処理でOffsetを使用する場合は、範囲外への移動を防ぐための条件を設定しておくことが重要です。

無効な範囲への移動について解説

パフォーマンスへの影響

大量のデータをOffsetを用いて操作すると、処理速度が遅くなる可能性があります。
範囲を正しく指定することや、必要に応じて一括処理を行うことでパフォーマンスの低下を防ぐ工夫が必要です。

パフォーマンスへの影響について解説

CurrentRegionと併用する場合の注意ポイント

CurrentRegionとOffsetを組み合わせて使用する際、「最終行」や「最終列」の空白まで取得してしまいます。
特にテーブルのデータを2次元配列で取得する際に、期待通りの範囲を取得できないことがあります。
その場合は、さらに「Resize プロパティ」を使用してセル範囲を変更する必要があります。

CurrentRegionと併用する場合の注意ポイントについて解説

「Offsetプロパティ」の使用例をご紹介

ここからは、「Offset」プロパティの具体的な使用例を紹介します。

【　使用例目次　】

使用例1: offsetを使用し、新しい行にデータを追加する

特定のセルを基点として、一つ下の新しい行にデータを追加する方法を紹介します。

主な使用用途 :
このコードは、Excelの指定した列で、既存のデータが入力されている最終行を特定し、その下の行に新しいデータを追加するために使用します。
新しいデータを継続的に追加していく必要がある作業で特に便利です。

Sub AddNewData()
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").Offset(lastRow).Value = "新しいデータ"
End Sub
動作概要
  • STEP1
    最終行の特定

    変数「lastRow」を宣言し、指定した列（この場合は列”A”）の最終行を取得します。
    この操作により、データが入力されている最後のセルを特定します。

  • STEP2
    データの追加

    Range(“A1”).Offset(lastRow) を使用して、最終行の下にあるセルを選択し、そのセルに新しいデータを追加します。
    この例では、「新しいデータ」という文字列を追加しています。

処理結果

使用例1の結果

このコードで使用している機能

1行ずつ解説

コードの詳細
  1. Sub AddNewData()
    • この行は、新しいサブルーチン「AddNewData」を定義しています。
      このサブルーチンは、指定された列の最終行を特定し、その下の行に新しいデータを追加するための機能を持っています。
  2. Dim lastRow As Long
    • ここでは、変数「lastRow」を Long 型で宣言しています。
      変数「lastRow」は、指定した列でデータが入力されている最後の行番号を保持するための変数です。
  3. lastRow = Cells(Rows.Count, “A”).End(xlUp).Row
    • この行は、指定された列「A」でデータが入力されている最後の行番号を取得しています。
      「Cells(Rows.Count, “A”).End(xlUp).Row」は、シートの最終行から上方向に向かって最初にデータがあるセルを探し、その行番号を返します。
      この行番号が変数「lastRow」に格納されます。
  4. Range(“A1”).Offset(lastRow).Value = “新しいデータ”
    • 「Range(“A1”).Offset(lastRow)」は、セル「A1」を基点に、変数「lastRow」だけ下方向に移動したセルを指します。
      このセルに対して、Value プロパティを使って「新しいデータ」という値を設定しています。
  5. End Sub
    • この行は、サブルーチンの終了を示しています。
      すべての処理が完了すると、このサブルーチンは終了し、プログラムは次のステップに進みます。

この行は、サブルーチンの終了を示しています。
すべての処理が完了すると、このサブルーチンは終了し、プログラムは次のステップに進みます。

目次に戻る

使用例2: offsetを使用し、ループを使用してデータを入力していく

基準セルから相対的な位置のセルを動的に操作しながら、データを処理する方法を紹介します。
※この処理は「Cells」を使用しても実装可能です。

主な使用用途 : 「データのループ処理」「一定の条件に基づいたデータの変更」

Sub LoopThroughCells()
    Dim i As Long
    
    For i = 1 To 10
        Range("A1").Offset(i, 0).Value = i
    Next i
End Sub
動作概要
  • STEP1
    変数 i の宣言

    カウンタ変数「i」を Long 型で宣言し、ループ処理で使用します。

  • STEP2
    ループ処理

    For i = 1 To 10 というループを設定し、「1」から「10」までの値を変数「i」に順に割り当てます。
    このループ内で、基準セルから「Offset」を使って「i」行下のセルを選択し、そのセルに「i」の値を入力します。

  • STEP3
    次のセルにデータを入力

    ループが終了するまで、セル「A1」を基準にした範囲の各セルに、「1」から「10」までの値が順に入力されます。

処理結果

使用例2の結果

このコードで使用している機能

1行ずつ解説

コードの詳細
  1. Sub LoopThroughCells()
    • この行は、新しいサブルーチン「LoopThroughCells」を定義しています。
      このサブルーチンは、指定したセルから開始して、ループ処理を使いながら複数のセルにデータを入力するための機能を持っています。
  2. Dim i As Long
    • ここでは、カウンタ変数「i」を Long 型で宣言しています。
      「i」は、ループの中で使用される変数で、各ループの繰り返しごとに値が「1」ずつ増加します。
  3. For i = 1 To 10
    • この行は、For ループを開始します。
      「i」の値が「1」から「10」まで変化し、それぞれの値に対してループ内の処理が実行されます。
  4. Range(“A1”).Offset(i, 0).Value = i
    • この行は、セル「A1」を基点として、「i」行下にあるセルに「i」の値を入力します。
      Offset(i, 0) は、基準セルから「i」行下に移動することを意味し、列方向の移動は行わないため、「0」と指定されています。
  5. Next i
    • この行は、For ループの終わりを示し、「i」の値を「1」増加させてループを繰り返します。
      「i」が「10」になるとループが終了します。
  6. End Sub
    • この行は、サブルーチンの終了を示しています。
      すべての処理が完了すると、このサブルーチンは終了し、プログラムは次のステップに進みます。

このコードは、Excelの指定したセルから始まる範囲に対して、ループを使ってデータを順次入力する際に非常に有効です。
ループ処理を使うことで、セルごとに手動で値を入力する手間を大幅に省くことができます。

特に、繰り返し作業が必要な場合や、大量のデータを入力する際に、このような自動化は作業効率を飛躍的に向上させます。
また、Offsetプロパティを活用することで、基準セルからの相対的な位置にデータを入力することができ、他のVBAコードとの組み合わせも容易になります。

目次に戻る

使用例3: テーブルのヘッダーを残してデータを削除する

テーブル（表）のデータをクリアする際に、OffsetとCurrentRegionを組み合わせて、ヘッダーは残し、データ部分のみを削除する方法を紹介します。

主な使用用途 :
このコードを実行すると、指定したテーブルのヘッダー行はそのまま残り、データ部分だけがクリアされます。
新たなデータを追加する前に、テーブルを初期化したい場合に非常に役立ちます。

Sub ClearTableData()
    Dim headerRange As Range
    Dim dataRange As Range

    '// ヘッダー範囲を取得（テーブルの最初の行）
    Set headerRange = Range("B2").CurrentRegion.Rows(1)

    '// データ範囲を取得（ヘッダー以外のすべての行）
    Set dataRange = headerRange.Offset(1, 0).Resize(headerRange.CurrentRegion.Rows.Count - 1)

    '// データ部分のみをクリア
    dataRange.ClearContents
End Sub
動作概要
  • STEP1
    ヘッダー範囲の取得

    Range(“B2”).CurrentRegion.Rows(1) を使用して、テーブルの最初の行、つまりヘッダー行を取得します。
    この範囲は「headerRange」というオブジェクト変数に格納されます。

  • STEP2
    データ範囲の取得

    headerRange.Offset(1, 0).Resize(headerRange.CurrentRegion.Rows.Count – 1) を使用して、ヘッダー行を除いたデータ部分の範囲を取得します。
    「Offset」でヘッダーの次の行から開始し、「Resize」でデータ範囲を調整します。
    この範囲はオブジェクト変数「dataRange」に格納されます。

  • STEP3
    データのクリア

    dataRange.ClearContents を使用して、データ部分のみをクリアします。
    ヘッダーはそのまま残り、テーブルの内容がリセットされます。

処理結果

使用例3の解説

このコードで使用している機能

詳細解説

コードの詳細
  1. Sub ClearTableData()
    • この行は、新しいサブルーチン「ClearTableData」を定義しています。
      このサブルーチンは、テーブル形式のデータのヘッダーを保持しつつ、データ部分のみをクリアするための機能を持っています。
  2. Dim headerRange As Range
    • ここでは、「headerRange」という名前のオブジェクト変数を Range 型で宣言しています。
      「headerRange」は、テーブルのヘッダー行の範囲を保持するための変数です。
  3. Dim dataRange As Range
    • 同様に、「dataRange」という名前のオブジェクト変数を Range 型で宣言しています。
      「dataRange」は、テーブルのデータ部分の範囲を保持するための変数です。
  4. Set headerRange = Range(“B2”).CurrentRegion.Rows(1)
    • この行は、セル「B2」を基点として、テーブルの最初の行（ヘッダー行）を取得し、それをオブジェクト変数「headerRange」に格納します。
      「CurrentRegion プロパティ」を使用することで、テーブル全体の範囲を取得し、その中の最初の行を Rows(1) で指定しています。
  5. Set dataRange = headerRange.Offset(1, 0).Resize(headerRange.CurrentRegion.Rows.Count – 1)
    • この行は、ヘッダー行の次の行から始まるデータ部分の範囲を取得し、それを オブジェクト変数「dataRange」に格納します。
      Offset(1, 0) は、ヘッダー行から1行下に移動するために使用されます。
      また、「Resize」を使って、データ範囲を正確に調整しています。
      「headerRange.CurrentRegion.Rows.Count – 1」により、ヘッダー行を除いたデータ行数が計算されます。
  6. dataRange.ClearContents
    • この行は、オブジェクト変数「dataRange」に格納されている範囲内のセルの内容をすべてクリアします。
      「ClearContents メソッド」を使うことで、セルの書式や枠線を残したまま、データだけを削除します。
  7. End Sub
    • この行は、サブルーチンの終了を示しています。
      すべての処理が完了すると、このサブルーチンは終了し、プログラムは次のステップに進みます。

このコードは、Excelのテーブルからデータをクリアする際に、ヘッダー行を残しつつ、データ部分のみを削除したい場合に非常に有効です。
データを一新する際に、テーブルの構造や書式を保持しつつ、内容だけをリセットできるため、頻繁にデータを更新する場面で便利です。

「Offset」と「Resize」を組み合わせて使用することで、データ範囲を柔軟に操作できるため、様々なテーブル構造に対応可能です。
このコードを活用することで、効率的なデータ管理が実現できます。

目次に戻る

まとめ

「Offsetプロパティ」は、Excel VBAでセルの相対位置を柔軟に取得し、さまざまな操作を効率的に行うために非常に便利なツールです。

このプロパティを使うことで、データの追加や削除、テーブルの管理が簡単になり、手動で範囲を設定する必要がなくなります。
作業の自動化が進み、効率的なデータ操作が可能になります。

ポイントのおさらい

この記事の使用例を実践することで、VBAの「Offsetプロパティ」を使いこなせるようになります。

まずは、サンプルコードをコピーして実行し、少しずつ自分の業務に応じたアレンジを加えていきましょう。

タイトルとURLをコピーしました