これだけはおさえるセル操作(2)−Resizeで自由自在|Excel VBA |
セル(範囲)に関する基本事項で、RangeとCellsをマスターしたら、Offsetプロパティと一緒に、ぜひResizeプロパティの使い方をマスターしてください。
【基礎編】
Resizeプロパティはセル操作の重要基本テクニックの1つです。その名のとおり、リサイズ(セル範囲のサイズ変更)を行い、サイズ変更した後のセル範囲を返します。使い方は次のとおりです。
Rangeオブジェクト.Resize(変更後の行数, 変更後の列数)
Offsetプロパティが現在の位置と移動後の位置との差分を引数で渡すのに対し、Resizeプロパティは差分ではなく変更後の行数・列数を渡します。
選択範囲を行・列ともに1ずつ拡張する場合は、次のように記述します。
Selection.Resize(Selection.Rows.Count + 1, Selection.Columns.Count + 1).Select
この例では選択範囲を拡張していますが、縮小する場合には現在の選択範囲の行数・列数を上記のようにカウントしたうえで、縮小したい行数・列数をマイナスすればOKです。
また、Reizeプロパティは引数を省略すると、元のセル範囲の行数・列数を引き継ぎます。そのため、行だけを拡張、または列だけを拡張するには次のように記述できます。
Selection.Resize(Selection.Rows.Count + 1).Select '行だけを拡張
Selection.Resize(, Selection.Columns.Count + 1).Select '列だけを拡張
ただ、この例のように、Resizeを使用するときは1行のコードが長く複雑なものになりがちです。わかりやすいコードにするためには、変更後の行数・列数をいったん変数に格納しておいてからリサイズを行うのも1つの方法です。行・列を1行ずつ拡張するコードを書きかえると次のようになります。
Dim cntRow As Long
Dim cntCol As Long
cntRow = Selection.Rows.Count + 1
cntCol = Selection.Columns.Count + 1
Selection.Resize(cntRow, cntCol).Select
選択範囲の拡張、縮小はOffsetプロパティを使うことでも可能でした。Offsetプロパティを使った方法は「これだけはおさえるセル操作(1)−Offsetで自由自在」を確認してください。
【応用編】
ワークシート上のデータ入力範囲(A列−E列)をリサイズしてE列を除いたデータをコピーしたい、という場合は、次のようにします。
Sub Sample1()
Dim cntRow As Long
Dim cntCol As Long
Dim Rng As Range
Set Rng = ActiveSheet.Range("A1").CurrentRegion
cntRow = Rng.Rows.Count
cntCol = Rng.Columns.Count - 1
Rng.Resize(cntRow, cntCol).Copy Destination:=Sheets("Sheet2").Range("A1")
End Sub
変数を使わずに、Withステートメントを使ってまとめて書くと次のようになります。
Sub Sample2()
With ActiveSheet.Range("A1").CurrentRegion
.Resize(.Rows.Count, .Columns.Count - 1).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End With
End Sub
Offsetプロパティとの組み合わせでさらに複雑な操作を行うことができます。
よく用いられるテクニックとしては、1行目が項目行である場合に、データ範囲のみ取得する方法、があげられます。データシート上では多くの場合1行目が項目行であるため、この1行をのぞいて処理をしたいというケースです。
考え方は上の例とほとんど変わりません。
まず、データ範囲をCurrentRegionで取得します。そのあとにOffsetで対象セル範囲を1行下にずらし、Resizeで1行削るのです。
Sub Sample3()
Dim cntRow As Long
Dim cntCol As Long
Dim Rng As Range
Set Rng = ActiveSheet.Range("A1").CurrentRegion
cntRow = Rng.Rows.Count - 1
cntCol = Rng.Columns.Count
Set Rng = Rng.Offset(1, 0) '※1行下にずらす
Set Rng = Rng.Resize(cntRow, cntCol) '※1行削る
Rng.Copy Destination:=Sheets("Sheet2").Range("A2")
End Sub
また、※印の部分はOffset、Resizeを2行に分けましたが、次のようにまとめることもできます。また、ResizeとOffsetを逆にすることもできます。
Sub Sample4()
With ActiveSheet.Range("A1").CurrentRegion
.Offset(1, 0).Resize(.Rows.Count - 1).Copy _
Destination:=Sheets("Sheet2").Range("A2")
End With
End Sub