Excel VBA Resizeプロパティの使い方：セル範囲を動的に変更する方法

VBAのResizeとは VBA
2024.09.02

「特定のセル範囲をもっと広げたいけど、どうやって指定すればいいんだろう…」
そんな疑問を持っていませんか？

お悩みポイント
悩む人
  • 任意のセル範囲を動的に拡張・縮小したい
  • 手動でセル範囲を調整する手間を省きたい
  • データ範囲を自在に操作できるようにしたい

この記事では、VBAの「Resizeプロパティ」を使って、セル範囲を動的に変更する方法を詳しく解説します。

「Resizeプロパティ」を使用することで、Excelでのセル範囲操作が簡単になり、データ処理の効率を大幅に向上させることができます。

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

【　この記事の概要　】

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

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

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

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

VBA Resizeプロパティの概要と基本構文

「Resizeプロパティ」は、Excel VBAで特定のセル範囲の行数や列数を動的に変更するために使用されるプロパティです。

例えば、既存のセル範囲を拡張してデータを追加したり、逆に範囲を縮小してデータを整理する場合に非常に便利です。

Resizeプロパティで何ができるか？

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

設定解説
  • 基準セル
    • Resizeを適用する範囲の起点となるセル範囲。
      基準セルは、通常RangeやCellsを指定します。
      これにより、どのセルを起点に範囲を変更するかを決定します。
  • 行数
    • 新しい範囲に含める行数。
      正の値のみ入力できます。 ※負の値はエラーになります
      省略すると基準範囲の行数が使用されます。

      行数のみ指定したい場合は、「Resize(2)」または「Resize(2, 0)」と指定します。
  • 列数
    • 新しい範囲に含める列数。
      正の値のみ入力ができます。 ※負の値はエラーになります
      省略すると基準範囲の列数が使用されます。

      列数のみ指定したい場合は、「Resize(0, 2)」または「Resize(, 2)」と指定します。

これにより、Excel VBAでのセル範囲の変更が簡単になります。

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

VBA Resizeプロパティの使い方

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

まず、セル「B2」を基点として、その範囲を「3行」「4列」に変更する例を見てみましょう。

Sub ResizeRange()
    Range("B2").Resize(3, 4).Select
End Sub

処理結果

このコードを実行すると、セル「B2」を基点に、範囲が「下に3行」「右に4列」拡張され、セル「B2」から「E4」までが選択されます。

元の範囲を変更して、新たな範囲を指定するのが「Resizeプロパティ」の特徴です。

VBA Resizeがよく使われる事例

セル範囲を動的に変更できる「Resizeプロパティ」は、以下のような場面でよく使用されます。

よく使われる事例
  • データの追加や削除の準備
    • 新しいデータを追加する際、既存のデータ範囲を広げて新しい行や列を追加できるスペースを作成したいときに便利です。

      例えば、新しいデータ列を追加したい場合、「Resizeプロパティ」を使って既存の範囲を広げ、そこに新しいデータを挿入できます。
      また、不要になった列を削除する場合にも、範囲を縮小してデータの整理を効率化できます。
  • 自動化されたデータの整理と最適化
    • 不要になったデータを取り除いて、範囲を小さく整理したいときに役立ちます。

      例えば、毎月のレポート作成時に、使用済みのデータ範囲を縮小して新しいデータのためのスペースを作ることができます。
      「Resizeプロパティ」を使って、不要な行や列を取り除き、データがきれいに整理された状態で処理を続けられます。
  • 変動するデータ量に対応した範囲の動的管理
    • 日々変わるデータを効率的に管理するために、範囲を自動的に調整したいときに使えます。

      例えば、日々増える売上データを管理する際、「Resizeプロパティ」を使用してデータ範囲を自動的に拡張し、新しいデータを常に最新の範囲で扱うことができます。
      これにより、データの管理が容易になり、最新データを常に反映させた分析が可能になります。
  • 特定範囲を動的に操作してデータを処理
    • テーブルの一部を取り出して、特定のデータだけを操作したいときに便利です。

      例えば、テーブル全体から特定の列や行を動的に取得し、その部分だけを操作する場合に「CurrentRegion」と「Offset」を組み合わせて範囲を指定し、その後「Resizeプロパティ」を使って必要な範囲を調整できます。
      これにより、必要なデータだけを効率的に処理することが可能です。

Resizeプロパティを使う際の注意ポイント

「Resizeプロパティ」は非常に便利ですが、初めて使う方にはいくつかの注意点があります。

これらのポイントを押さえることで、予期しないエラーを防ぎ、よりスムーズにVBAを活用できるようになります。

変更後のサイズがシートの外に出てしまうとエラーが発生

Resizeで指定する行数や列数が、シートの外に出てしまうとエラーが発生します。

シートには行番号の上限（Excelでは通常1,048,576行）や列の上限（通常16,384列）が存在します。
Resizeプロパティでこれらの上限を超えるような範囲を指定すると、「実行時エラー」が発生してしまいます。
※最終行・最終列はエクセルのバージョンで異なります。

事前に行数や列数がシート内に収まるかを確認するか、必要に応じて条件分岐を使ってエラーを防ぐようにしましょう。

Resizeプロパティで負の値を指定するとエラーが発生

Resizeプロパティで行数や列数に負の値 (マイナス)を指定すると、エラーが発生します。

「Resize(-1, -1)」のように行数や列数を負の値で指定すると、「実行時エラー」が発生し、コードが停止します。
負の値を指定すると範囲を減らそうとする動作が想定されますが、実際にはVBAは負の値をサポートしていないため、このようなエラーが発生します。

Resizeプロパティで範囲を縮小したい場合は、正の値を指定してその行数や列数を適切に設定するようにしましょう。
必要に応じて、Resizeプロパティの前に現在の範囲の行数や列数を確認し、負の値を使わないようにコードを工夫することが大切です。

他のプロパティ( CurrentRegion･Offset )と併用する場合は注意が必要

Resizeプロパティは、他のプロパティ（例：Offset）と併用することで、さらに柔軟なセル範囲操作が可能になりますが、併用時には注意が必要です。

例えば、CurrentRegionを使って表全体の範囲を取得した後にResizeプロパティを使うと、表のサイズに応じて範囲を動的に変更できます。
しかし、ResizeプロパティとOffsetを組み合わせた場合、Offsetによって基準がずれた状態でResizeプロパティが適用されるため、元の範囲から外れた範囲を操作してしまうことがあります。
また、コードの記述量が増えるため、間違いのないように注意しましょう。

VBA Resizeの基本的な使い方と具体例

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

以下に紹介する使用例は、すべてコピペしてすぐに実行できるコードです。
気になる例は、ぜひご覧いただき、実際に実行してみてください。

【　使用例目次　】

使用例1: 表のデータ部分のみ削除を行う (CurrentRegion･Offset･Resize)

表のデータ部分だけを削除し、ヘッダー行は残したままクリアする方法を紹介します。
この操作には、CurrentRegion、Offset、Resizeの3つのプロパティを組み合わせて使用します。

主な使用用途 : このコードは、Excelのテーブルからデータ部分のみを削除し、ヘッダー行はそのまま残す場合に使用します。
例えば、データを更新する際に、表の構造は保持しつつ、古いデータをクリアする場合に便利です。

Sub ClearTableData()
    With Range("B2").CurrentRegion
        .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).ClearContents
    End With
End Sub
動作概要
  • STEP1
    表全体の範囲を取得

    CurrentRegionプロパティを使って、セル「B2」を基点にしたテーブル全体の範囲を動的に取得します。
    この範囲には、表のヘッダー行も含まれます。

  • STEP2
    データ部分の範囲を特定

    Offsetプロパティを使って、表の1行目（ヘッダー行）を除いた2行目以降を基点に移動します。
    その後、Resizeプロパティで、ヘッダー行以外のすべての行を含む範囲に調整します。
    これにより、データ部分のみの範囲が特定されます。

  • STEP3
    データ部分をクリア

    ClearContentsメソッドを使って、特定されたデータ部分の範囲内のセルの内容をすべて削除します。
    これにより、表の構造やヘッダーはそのまま残り、データ部分だけがクリアされます。

処理結果

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

詳細解説

コードの詳細
  1. Sub ClearTableData()
    • この行では、新しいサブルーチン「ClearTableData」を定義しています。
      このサブルーチンは、テーブル内のデータ部分だけを削除するための機能を持っています。
  2. With Range(“B2”).CurrentRegion
    • この行では、セル「B2」セルを基点にしてテーブル全体の範囲を取得し、その範囲内で後続の操作を行う準備をしています。
      Withステートメントを使うことで、範囲に対して繰り返し操作を行いやすくしています。
  3. .Offset(1, 0).Resize(.Rows.Count – 1, .Columns.Count).ClearContents
    • この行では、以下の操作を行っています。
      • Offset(1, 0)
        • Offsetは、基準となるセルからどれだけ上下左右に動かすかを指定するプロパティです。
          ここでは、「1行下に移動し、列は動かさない」という指示を出しています。
          つまり、ヘッダー行をスキップして、その下の行（データ部分の最初の行）に移動しています。
      • Resize(.Rows.Count – 1, .Columns.Count)
        • Resizeは、指定したセル範囲のサイズを変更するプロパティです。
          ここでは、Offsetで移動した後の範囲を、テーブル全体の行数からヘッダー行を引いた数で再設定しています。

          行数の計算：「.Rows.Count」は、テーブル全体の行数を示します。
          ここでは、ヘッダー行を除くために「-1」しています。
          つまり、データ部分の行数だけを指定しています。

          列数の指定：「.Columns.Count」は、テーブル全体の列数を示しており、これはそのまま使用します。
          これにより、テーブルのデータ部分全体を含む範囲が指定されます。
        • .ClearContents
          • 特定された範囲内のセルの内容をクリアします。
            ClearContentsメソッドを使用することで、セルの書式や枠線は残したまま、セルのデータのみを削除します。
  4. End With
    • Withステートメントを終了し、範囲内での操作を完了します。
  5. End Sub
    • この行は、サブルーチンの終了を示しています。
      すべての処理が完了すると、このサブルーチンは終了し、プログラムは次のステップに進みます。

このコードは、Excelのテーブルからデータをクリアする際に、ヘッダー行を残しつつ、データ部分のみを削除したい場合に非常に有効です。

特に、定期的にデータを更新する場面で、テーブルの構造や書式を保持したまま内容をリセットするための効率的な方法です。
「CurrentRegion」、「Offset」、「Resize」プロパティを組み合わせて使用することで、柔軟に範囲を操作でき、さまざまなテーブル構造に対応可能です。

目次に戻る

使用例2: テーブルを縮小しデータ部分をクリアする (Resize･Offset)

テーブルの特定の範囲のデータをクリアする方法を紹介します。
この操作には、ResizeとOffsetのプロパティを組み合わせて使用します。

主な使用用途：
このコードは、テーブルの一部を除外して、特定の範囲だけをクリアしたい場合に使用します。
例えば、最初の2行を残し、それ以外のデータをクリアしたい場合に便利です。

Sub ShrinkTableAndClearData()
    With Range("B2").CurrentRegion
        .Resize(.Rows.Count - 2, .Columns.Count).Offset(2).ClearContents
    End With
End Sub
動作概要
  • STEP1
    表全体の範囲を取得

    CurrentRegionプロパティを使用して、セル「B2」を基点にしたテーブル全体の範囲を動的に取得します。

  • STEP2
    テーブル範囲を縮小し、操作対象を設定

    Resizeプロパティで、テーブルの行数を2行減らして範囲を縮小します。その後、Offsetプロパティで縮小したテーブル範囲を2行下にずらします。この結果、元の範囲から最初の2行を除いた部分だけが選択されます。

  • STEP3
    選択された範囲のデータをクリア

    ClearContentsメソッドを使用して、選択された範囲内のセルの内容をすべてクリアします。これにより、テーブルの最初の2行は残り、それ以外のデータが削除されます。

処理結果

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

1行ずつ解説

コードの詳細
  1. Sub ShrinkTableAndClearData()
    • ここの行では、新しいサブルーチン「ShrinkTableAndClearData」を定義しています。
      このサブルーチンは、テーブルの範囲を縮小し、特定のデータ部分のみをクリアするための機能を持っています。
  2. With Range(“B2”).CurrentRegion
    • この行では、「B2」セルを基点にしてテーブル全体の範囲を取得し、その範囲に対して操作を行う準備をしています。
      「Withステートメント」を使うことで、後続の操作がこの範囲内で実行されるようになります。
  3. .Resize(.Rows.Count – 2, .Columns.Count)
    • この行では、テーブルの行数を縮小する操作を行っています。
      • Resize(.Rows.Count – 2, .Columns.Count):
        • 「Resizeプロパティ」を使用して、テーブル全体の行数を2行減らします（Rows.Count – 2）。
          これにより、元のテーブルの最後の2行が除外された範囲が選択されます。
      • .Offset(2)
        • 「Offsetプロパティ」を使って、縮小された範囲をさらに2行下に移動します。
          これにより、最初の2行をスキップして、それ以外の行（この場合は3行目以降）が選択されます。
      • .ClearContents
        • 「ClearContentsメソッド」を使用して、選択された範囲内のセルの内容をすべてクリアします。
          これにより、指定したデータ部分が削除され、セルの書式や枠線はそのまま残ります。
  4. End With
    • Withステートメントを終了し、範囲内での操作を完了します。
  5. End Sub
    • この行は、サブルーチンの終了を示しています。
      すべての処理が完了すると、このサブルーチンは終了し、プログラムは次のステップに進みます。

このコードは、Excelのテーブルから特定の範囲だけをクリアする際に非常に便利です。

特に、最初の数行を残して、それ以外のデータを削除したい場合に適しています。
Resizeプロパティを使ってテーブルの範囲を縮小し、Offsetプロパティでその範囲をずらすことで、必要なデータ部分だけを選択して操作できます。

これにより、特定のデータを保持しつつ、不要なデータを効率的に削除することが可能です。
VBAを使ったテーブル操作の幅が広がり、データ管理がさらに柔軟になります。

目次に戻る

使用例3: 配列の要素数に応じたセル範囲の動的設定 (Resize･Ubound)

VBAのResizeプロパティとUbound関数を組み合わせて、配列の要素数に応じてセル範囲を動的に設定する方法を紹介します。

主な使用用途：
このコードは、Excelのシートに配列の要素を自動的に配置する場合に使用します。
例えば、配列に含まれるデータの数に応じてセル範囲を自動的に調整し、効率的にデータをシートに展開したいときに便利です。

Sub ResizeWithUbound()
    Dim myArray() As Variant
    myArray = Array("リンゴ", "バナナ", "オレンジ", "パイナップル")

    '// 配列の要素数に応じてセル範囲を設定し、値を入力
    Range("A1").Resize(UBound(myArray) + 1, 1).Value = WorksheetFunction.Transpose(myArray)
End Sub
動作概要
  • STEP1
    配列の作成

    最初に、「myArray」という文字列配列を作成し、いくつかの果物名を格納します。
    ここでは4つの要素が含まれています。

  • STEP2
    Uboundで配列の要素数を取得

    UBound(myArray)で配列の要素数（上限インデックス）を取得します。
    VBAでは、配列のインデックスは「0」から始まるため、例えば4つの要素がある場合、UBound(myArray)は「3」を返します。

  • STEP3
    Resizeプロパティでセル範囲を調整

    Range(“A1”).Resize(UBound(myArray) + 1, 1)により、「A1」セルを基点として、配列の要素数に応じた範囲が設定されます。
    「+1」を行うことで、インデックスの「0」から始まる特性を考慮し、正確な行数が設定されます。

  • STEP4
    配列のデータをセルに入力

    WorksheetFunction.Transpose(myArray)を使って、配列を縦方向に展開し、指定したセル範囲にデータを入力します。
    これにより、配列の各要素が「A1」セルから下方向に順に配置されます。

処理結果

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

  • Array (配列の作成)
  • UBound (配列の上限インデックス取得)
  • Transpose (配列を縦方向に変換)

1行ずつ解説

コードの詳細
  1. Sub ResizeWithUbound()
    • これは、VBAで新しいサブルーチン（マクロ）を定義するための行です。「ResizeWithUbound」という名前のサブルーチンを作成しています。
      このサブルーチンの中に、実行したい一連の処理を書きます。
  2. Dim myArray() As Variant
    • ここでは、「myArray」という名前の配列を宣言しています。
      Variant型を使うことで、配列には異なるデータ型の値（例えば文字列や数値など）を混在して格納できます。
      Dimは「宣言する」という意味です。配列は複数のデータを1つの変数にまとめて管理するために使用されます。
      Array関数を使用する際は、Variant型を使用しないとエラーとなります。
  3. myArray = Array(“リンゴ”, “バナナ”, “オレンジ”, “パイナップル”)
    • この行では、myArray配列に4つの果物名（「リンゴ」、「バナナ」、「オレンジ」、「パイナップル」）を格納しています。
      Array関数を使って、複数の値を一度に配列に入れています。
      この配列にはVariant型が適用されており、様々なデータ型が許容されますが、ここではすべて文字列を使用しています。
  4. Range(“A1”).Resize(UBound(myArray) + 1, 1).Value = WorksheetFunction.Transpose(myArray)
    • Range(“A1”)
      • まず、ExcelシートのセルA1を基点として操作を行うことを示しています。
        ここからデータを入力する範囲が始まります。
    • Resize(UBound(myArray) + 1, 1)
      • Resizeは、セル範囲のサイズを動的に変更するためのプロパティです。
        ここでは、UBound(myArray) + 1により、配列の要素数に合わせて行数を設定します。
        UBound(myArray)は、配列の最後の要素のインデックスを返します。
        配列のインデックスは通常「0」から始まるため、「+1」して正確な行数を設定しています。
        「1」は列数を示し、1列だけを使用することを意味します。
    • Value = WorksheetFunction.Transpose(myArray)
      • 最後に、配列の内容をセルに入力します。
        WorksheetFunction.Transpose(myArray)を使って、配列を縦方向に変換し、指定したセル範囲にデータを入力します。
        これにより、配列の各要素が「A1」セルから下方向に順に配置されます。
  5. End Sub
    • これはサブルーチンの終了を示す行です。
      Subで始まるすべての処理がここで完結します。
      これ以降のコードは別の処理として扱われます。

このコードは、Variant型の配列に格納されたデータをExcelのシート上に縦方向に自動で配置する方法を示しています。

Resizeプロパティを使って、配列の要素数に合わせてセル範囲を動的に調整し、Transpose関数でデータを縦方向に展開します。
これにより、データ量が変わっても自動的に対応でき、柔軟にデータをシートに配置することが可能です。

目次に戻る

まとめ

「Resizeプロパティ」は、Excel VBAでセル範囲のサイズを動的に変更し、データの管理や操作を効率的に行うために非常に便利なツールです。

このプロパティを使うことで、範囲の拡張や縮小を簡単に行い、手動で範囲を設定する手間を省くことができます。作業の自動化が進み、効率的なデータ操作が可能になります。

ポイントのおさらい

  • Resizeプロパティを使って、セル範囲を動的に変更する方法を理解する
  • Resizeプロパティがよく使われる場面を理解する
  • Resizeプロパティを使う際の注意点を理解する
  • Resizeプロパティの使用例を確認し、実践的に利用する方法を学ぶ

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

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

この記事が役に立ったと思った方は、ぜひサイトマップから違う記事も参照ください。

サイトマップ
VBAワークシート関数 VBA 基本設定 VBAとは？ VBAの始め方 VBAのおすすめ設定 セル操作 RangeとCells CurrentRegion Offset Resize 最終行・最終列の取得 変数 変数 オブジェクト変数 グロ...
zangyo-free.com
2024.08.28

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