【ExcelVBA】セル範囲を縮小する/見出し行を除外する

セル範囲を縮小する方法と、それにより見出し行を除外する方法を紹介します。

セル範囲の「下側」や「右側」を縮小する
セル範囲の「上側」や「左側」を縮小する
おまけ：汎用関数を作って持っておく

セル範囲の「下側」や「右側」を縮小する

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

Resizeプロパティは「左上のセルを基準に範囲を取りなおす」プロパティなので、
下側や右側の範囲を拡大縮小するのは、以下のコードで行えます。

Set 右にx下にy拡大したセル範囲 = セル範囲.Resize(セル範囲.Rows.Count + y _
                                                                 , セル範囲.Columns.Count + x)

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

このコードは拡大縮小どちらもできるコードで、例えば下を1行縮小する場合は、

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

と、yにマイナス値を入れればOKです。

列は大きさを変えませんので、Resizeプロパティの第2引数は省略可能です。

このコードを詳しく見たい方は、こちらの記事へどうぞ

www.limecode.jp

セル範囲の「上側」や「左側」を縮小する

上側や左側を縮小したい場合は、左上のセルを基準としているResizeプロパティ単体では取得できません。

このため、例えば上側を1行縮小したセル範囲は、以下のコードで取得します。

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

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

結果的に、第1行だけが除外されたセル範囲が出来上がりますね。

このコードの一番の使い道は、記事タイトルの通り「見出し行の除外」です。

表データ

↑このような表を扱う際、CurrentRegionプロパティがとても便利で、

Range("A2").CurrentRegion ' A2:E7が取得される

と表全体のエリアを一発で取得することができます。

「ActiveSheet.Autofilter.Range」でも同じことができますね。

このように表全体を取得するプロパティはとても便利なのですが、
しかし、実際に処理をするとなると、"A3:E7"が欲しくなります。

このエリアを取得するときに、今回の手法を用いることができますね。

Dim 表全体 As Range, データエリア As Range

Set 表全体 = Range("A2").CurrentRegion
Set データエリア = 表全体.Offset(1).Resize(表全体.Rows.Count - 1)

おまけ：汎用関数を作って持っておく

今回のコードを中間変数なしでやろうとすると、

Set データエリア = Range("A2").CurrentRegion.Offset(1).Resize(Range("A2").CurrentRegion.Rows.Count - 1)

こうなって、ちょっと何言ってるかわかんないコードになります。

こういった冗長なコードは、汎用関数を作ることでスッキリ書けるようになります。

' すべてのマクロで使いまわせるような関数を作っておく
Function セル範囲の上部をn行除外(元のセル範囲 As Range, n As Long) As Range
    Set セル範囲の上部をn行除外 = 元のセル範囲.Offset(n).Resize(元のセル範囲.Rows.Count - n)
End Function

' 実行例
Set データエリア = セル範囲の上部をn行除外(Range("A2").CurrentRegion, 1)

　
そこそこ見やすくなりましたね。

ただ、今回の「セル範囲の上側を除外」はあまり頻出のコードではないので、
「表の見出しを除外する」くらいでしかやらない方も多いと思います。

その場合は、ストレートに下の関数にした方が便利です。

Function CurrentRegion見出し除外(起点セル As Range) As Range
    With 起点セル.CurrentRegion
        Set CurrentRegion見出し除外 = .Offset(1).Resize(.Rows.Count - 1)
    End With
End Function

' 実行例
Set データエリア = CurrentRegion見出し除外(Range("A2"))

これで相当見やすくなりましたね。

関数の汎用性はだいぶ狭まりましたが、この処理をたくさんやる人にとってはこちらの方が便利です。

もちろんAutoFilterに対しても同じように関数を作ることができ、

Function GetRangeオートフィルターのデータ部分(指定シート As Worksheet) As Range
    With 指定シート.AutoFilter.Range
        Set GetRangeオートフィルターのデータ部分 = .Offset(1).Resize(.Rows.Count - 1)
    End With
End Function

' 実行例
Set データエリア = GetRangeオートフィルターのデータ部分(ActiveSheet)