大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAでRangeで取得した範囲を変更するResizeについてご紹介します。Resizeは、取得したセル範囲の行や列の大きさを、変更することができるVBA関数です。Resizeが使える場面について、具体的なVBAコードを使って解説してきます。

はじめに

この記事では、セル範囲を変更するResizeについて、ご紹介します。

Resizeを使えば、取得したセル範囲の行や列を変更することができます。

表のデータから値だけを取得したい場合や、配列をセルに入力したい場合に、使える関数です。

では、具体的なVBAコードを使って、Resizeについて、解説していきます。

この記事で紹介すること

  • セル範囲を変更するResizeの使い方
  • Resizeの実用例

目次から見たい項目へ移動すると便利ですよ。

目次

ポイントとなるVBAコード

初めに、ポイントとなるVBAコードを記載しておきます。

VBAコードだけ確認したい場合に、ご活用ください。

With ActiveSheet
    .Range("A1").Resize(4, 3).Select '4行3列に変更
    .Range("A1:C4").Resize(1).Select '1行に変更
    .Range("A1:C4").Resize(, 1).Select '1列に変更
End With

では、解説していきます。

VBAでRangeの範囲を変更する

Rangeで取得したセル範囲を変更するのに、『Resize』に使います。

まずは、Resizeの使い方を、具体的なVBAコードを使ってご説明します。

Resizeの使い方

セル範囲の大きさを変更する『Resize』へは、次のように入力します。

行と列の大きさを変更

『Range("A1").Resize(行, 列)』

という感じです。

行と、列には、それぞれ次の値を入力します。

  • 行:変更後の行数
  • 列:変更後の列数

行の大きさだけを変更

『Range("A1:C4").Resize(行)』

  • 行:変更後の行数

行だけを入力すると、列の大きさはそのままで、行の大きさだけを変更することができます。

列の大きさだけを変更

では、VBAコードを作って、Resizeを使ってみます。

『Range("A1:C4").Resize(, 列)』

  • 列:変更後の列数

列だけを入力すると、行の大きさはそのままで、列の大きさだけを変更することができます。

4行3列に変更する

セル範囲Range("A1")を、Resizeを使って、4行3列に変更してみます。

Sub TEST1()
    
    'セル範囲を4行3列にする
    ActiveSheet.Range("A1").Resize(4, 3).Select
    
End Sub

実行してみます。

セル範囲を4行3列に変更

Resizeを使って、セル範囲を4行3列に変更した結果

3行4列のセル範囲が選択されました。

セル範囲の行と列を変更したい場合に、使えます。

1行に変更する

セル範囲Range("A1:C4")を、Resizeを使って、1行だけに変更してみます。

Sub TEST2()
    
    'セル範囲を1行にする
    ActiveSheet.Range("A1:C4").Resize(1).Select
    
End Sub

実行してみます。

セル範囲を1行に変更

Resizeを使って、セル範囲を1行だけに変更した結果

セル範囲Range("A1:C4")の、1行だけが選択されています。

セル範囲の中から、1行だけを取得したい場合に使えます。

1列に変更する

セル範囲Range("A1:C4")を、Resizeを使って、1列だけに変更してみます。

Sub TEST3()
    
    'セル範囲を1列にする
    ActiveSheet.Range("A1:C4").Resize(, 1).Select
    
End Sub

実行してみます。

セル範囲を1列に変更

Resizeを使って、セル範囲を1列だけに変更した結果

セル範囲Range("A1:C4")の、1列だけが選択されています。

セル範囲の中から、1列だけを取得したい場合に使えます。

VBAで表の値だけを取得する

表の値だけを選択するというのをやってみます。

Resizeと、CurrentRegionやOffsetなどを組み合わせて、VBAコードを作ります。

表のデータ

次のような表を作ってみました。

作ってみた表

作ってみた表

この表の値だけを取得してみます。

ResizeとOffsetを使って選択

表の値だけを取得するVBAコードはこちらになります。

Sub TEST4()
    
    '表のセル範囲を取得
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    '表の値だけを選択
    a.Resize(a.Rows.Count - 1).Offset(1, 0).Select
    
End Sub

まずは、実行して結果を見てみます。

表の値だけを取得

ResizeとOffsetを使って、表の値だけを取得した結果

表の値だけが選択されました。

選択できているので、選択したセル範囲を変数に代入すると、配列として値を取得できます。

流れとしては、

  • ①表のセル範囲を、CurretRegionで取得
  • ②セル範囲の行を、Resizeで1行だけ減らす
  • ③セル範囲を、Offsetで1行だけ下に移動する

というような手順です。

手順①:表のセル範囲を、CurrentRegionで取得

CurrentRegionを使うと、同じ表と認識したセル範囲を、取得することができます。

こんな感じで取得できます。

Sub TEST5()
    
    '表のセル範囲を取得
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    '表の全てを選択
    a.Select
    
End Sub

実行してみます。

CurrentRegionでセル範囲を取得した結果

表のセル範囲を選択することができました。

手順②:セル範囲の行を、Resizeで1行だけ減らす

ここで、Resizeの出番です。

値だけを取得したいので、行数を1行だけ減らします。

Resizeを使って、1行だけ行数を減らしてみます。

Sub TEST6()
    
    '表のセル範囲を取得
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    'セル範囲を1行減らして選択
    a.Resize(a.Rows.Count - 1).Select
    
End Sub

ここで、セル範囲の行数は、『.Rows.Count』で取得できます。

1行減らすので、『.Rows.Count - 1』としています。

実行して、1行減らしたセル範囲を見てみます。

Resizeを使ってセル範囲を1行だけ減らした結果

セル範囲が、1行だけ小さくなりました。

ただ、これだけでは、値を取得できないので、最後にOffsetで1行だけ下に移動します。

手順③:セル範囲を、Offsetで1行だけ下に移動する

最後にOffsetで、1行だけ下に移動して完成です。

最初に記載したVBAコードです。

Sub TEST7()
    
    '表のセル範囲を選択
    Set a = ActiveSheet.Range("A1").CurrentRegion
    
    '表の値のみを選択
    a.Resize(a.Rows.Count - 1).Offset(1, 0).Select
    
End Sub

実行すると、表の値だけを取得できます。

表の値だけを取得した結果

表の値だけを取得できました。

こんな感じで、Resizeは使えます。

VBAで配列の値をセルに入力

もう一つ、Resizeが使える場面があります。

配列の値をセルに入力する場合です。

配列をセルに入力したい

セルに入力された値を配列に入力して、その配列を別のセルに入力する。というのがやりたいとします。

こんな感じのことをやります。

配列をセルに入力したい

値を取得して配列をセルに入力するということをやるイメージ図

値を取得して、別のセルに入力ですね。

別セルにコピーだけなら『Copy』を使えばいいです。

ただ今回は、値を取得して、何かしら値を変更して、配列としてセルに入力する。という場合を考えています。

Resizeを使わない場合

まず、Resizeを使わない場合です。

配列をセルに入力する場合は、Resizeを使わないと、ちょっとめんどくさいんです。

Sub TEST8()
    
    With ActiveSheet
        '表のセル範囲を取得
        a = .Range("A1").CurrentRegion
        'セルに入力
        .Range("E1:G4") = a '←ここがめんどくさい
    End With
    
End Sub

こんな感じになりますよね。

実行してみます。

配列をセルに入力

Resizeを使わないで値を別セルに入力

別セルに入力できました。

ただ、入力先のセル範囲をいちいち設定しないといけないので、めんどくさいです。

Resizeを使うと次のように、簡単になります。

Resizeを使って配列をセルに入力

Resizeを使って、セルの値を配列に入れて、別セルに入力する。というのをやってみます。

Sub TEST9()
    
    With ActiveSheet
        '表のセル範囲を取得
        a = .Range("A1").CurrentRegion
        'セルへ入力(セル範囲を定数で指定しなくていい)
        .Range("E1").Resize(UBound(a, 1), UBound(a, 2)) = a
    End With
    
End Sub

配列の大きさを、『Ubound』で調べて、『Resize』で大きさを変更しています。

ここで、Uboundは、

  • Ubound(配列, 1):配列の行数
  • Ubound(配列, 2):配列の列数

という感じで取得できます。

入力先のセル範囲に、定数がないですよね。ここがいいんです。

では、実行してみます。

配列をセルに入力

Resizeを使ってセルの値を取得して、別セルに入力した結果

別のセルに値を入力することができました。

元のセル範囲が変更になっても、同じVBAコードで、別セルに値を入力することができます。

こんな感じです。

元データの大きさを変更した場合

値の大きさを変更しても同じコードで別セルに入力できる

Resize、便利ですね。

おわりに

この記事では、セル範囲の大きさを変更する、Resizeについてご紹介しました。

Resizeを使えば、セル範囲の大きさを自由に変更することができます。

Resizeの大きさを自由に変更できる、ということを利用すると、表の値だけを取得したり、配列をセルに入力する際に使ったりと、便利に使うことができます。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す