セル範囲を元の大きさを基準に拡大縮小する

セル範囲を、元の大きさを基準に拡大縮小する方法を紹介します。

セル範囲を1行増やす(下側を拡張)

セル範囲を変更するには、RangeオブジェクトのResizeプロパティを使用します。

例えば、

このオレンジ色の売上データ部分は、

Range("E3").Resize(5, 1)
' または
Range("E3").Resize(5) ' ←列数は変わらないので省略可

で取得できます。

「第1セルからデータの数だけ下に伸ばす」がとても分かりやすくていいですね。

入っているデータの数が分かっているときは、読みやすく書きやすいコードです。

さて、目的は「これをセル範囲基準で実行する」ことです。

例えばレコードが追加されたときなどに、
「この表全体の範囲である"A3:E7"を1行下に広げるコード」はこのように書きます↓

Set セル範囲 = Range("A3:E7")
Set 下に1行広げたセル範囲 = セル範囲.Resize(セル範囲.Rows.Count + 1 _
                                                           , セル範囲.Columns.Count)

このコードで、"A3:E8"を取得することができます。

理屈は簡単で、Resize(元の行数 + 1,元の列数)ですね。

なお、元の大きさを変えない場合はその引数を省略できますので、

Set 下に1行広げたセル範囲 = セル範囲.Resize(セル範囲.Rows.Count + 1)

これでも目的を果たすことができます。
列を変えなくていい場合は、こちらが分かりやすいですので、こちらで書きましょう。

ちなみに、よく勘違いしやすいポイントとして、

Rnage("A3:E7").Resize(1)

は、"A3:E3"を取得します。

Resizeに渡した「1」は、+1という意味ではなく、純粋に1行という意味になります。

「単独のセル.Resize」のイメージで「拡大するプロパティ」と誤認しやすいのですが、
「Re」sizeプロパティですので、「範囲を取りなおすプロパティ」であることにご注意ください。

セル範囲を1行減らす(下側を縮小する)

Set 下から1行減らしたセル範囲 = セル範囲.Resize(セル範囲.Rows.Count - 1)

まったく同じ理屈で、元の行数-1をResizeプロパティに渡せば、
セル範囲の下側を縮小することができます。

セル範囲を1列増やす/減らす

Set 右に1列広げたセル範囲 = セル範囲.Resize(, セル範囲.Columns.Count + 1)
Set 右から1行減らしたセル範囲 = セル範囲.Resize(, セル範囲.Columns.Count -1)

こちらも同じですね。元の列数±1をResizeプロパティの第2引数に渡せば、
セル範囲の右側を拡大縮小できます。

行数を変えない場合、Resize(, ～)と、「(」の後で即座にカンマを打つことで、第1引数を省略できます。

汎用関数にする

この拡大縮小を一発でこなすプロパティがあればよかったのですが、それがないため、

Set セル範囲 = Range("A3:E7")
Set 下と右を1ずつ広げたセル範囲 = セル範囲.Resize(セル範囲.Rows.Count + 1, セル範囲.Columns.Count + 1)

という長ったらしいコードになってしまいました。

変数を用意しないと、

Set 下と右を1ずつ広げたセル範囲 = Range("A3:E7").Resize(Range("A3:E7").Rows.Count + 1, Range("A3:E7").Columns.Count + 1)

こうなって、わけわかめなコードになります。

こういった冗長なコードは、汎用関数にしてしまうとスッキリします↓

Function Rangeを拡大縮小(元のセル範囲 As Range, R方向 As Long, C方向 As Long) As Range
    With 元のセル範囲
        Set Rangeを拡大縮小 _
            = .Resize(.Rows.Count + R方向, .Columns.Count + C方向)
    End With
End Function

' 実行例
Set 下と右を１ずつ広げたセル範囲 = Rangeを拡大縮小(Range("A3:E7"), 1, 1)

すごく読みやすくなりましたね！

メインコードが読みやすくなったのももちろんですが、関数内でWithがキレイに使えていることも、ひとつのポイントです。

関数に分けると、その処理のためだけにWith、Exit Function、On Error Resume Nextなどが使えるようになります。

ちなみにこの関数は、このままだと「1行増やしたい」ときにも、

Set 下に１行広げたセル範囲 = Rangeを拡大縮小(Range("A3:E7"), 1, 0)

と、列の指定が必須になります。

これも省略したいので、

Function Rangeを拡大縮小(元のセル範囲 As Range, Optional R方向 As Long = 0 _
    , Optional C方向 As Long = 0) As Range
    With 元のセル範囲
        Set Rangeを拡大縮小 = .Resize(.Rows.Count + R方向, .Columns.Count + C方向)
    End With
End Function

と、Optionalキーワードを使って引数を省略可能にしましょう。

1行増やしたいときに、

Set 下に１行広げたセル範囲 = Rangeを拡大縮小(Range("A3:E7"), 1)

こう書けるようになります。是非使ってみてください。

こういったしつこいコードを見かけたら、関数化のポイントです。

理屈は簡単なくせに読むのも書くのも面倒なコードを見つけたら、
Functionのことを思い出してあげてください。

おまけ：上側と左側の拡大縮小について

セル範囲の行数や列数を増やす/減らすというと、
多くの場合は「下方向(レコードの追加)」と「右方向(情報の追加)」です。

しかし、たまに上側と左側をいじりたいということがあります。

CurrentRegionや、AutoFilter.Rangeなどの「表全体」から、
見出し行(第1行)を除外するときなどですね。

その時はResizeプロパティ単体では実現できませんので、

Set セル範囲 = Range("B3:E8")
Set 上から1行減らしたセル範囲 = セル範囲.Offset(1).Resize(セル範囲.Rows.Count - 1)

というコードを実行します。

Offsetプロパティで「表全体を大きさそのままに1つ下に移動」して、
Resizeプロパティで「その移動によってはみ出たおしりを削る」コードです。

知っていれば簡単なコードですが、思いつくのはなかなか難しいコードです。
この機会に、覚えてしまってください。