指定列のデータ部分を取得する-Resize/Intersect

特定の列のデータ部分をRangeオブジェクトとして取得する方法を解説します。

表中のこの部分の取得方法ですね。

主に4つの取得方法がありますので、状況に応じて便利なものを使用してください。

Range(Cells, Cells)

まずは基本形のRange(Cells, Cells)で取得するパターンです。

Dim ws対象シート As Worksheet
Set ws対象シート = ThisWorkbook.Worksheets("○○")

Dim 最終行 As Long
最終行 = ws対象シート.Cells(ws対象シート.Rows.Count, 5).End(xlUp).Row

Dim 売上列データ部 As Range
Set 売上列データ部 = ws対象シート.Range(ws対象シート.Cells(3, 5) _
                                                       , ws対象シート.Cells(最終行, 5))

このコードを実行することで売上列データ部にE3:E9を取得することができます。

最終行が必要となりますので、これを変数として取得しておきましょう。

基本となる必修コードではありますがややコードが冗長になりがちで、
しっかり変数を用意しないと読みづらいコードになりやすいです。

Worksheetの変数もしっかり用意した上で、
取得したセル範囲もしっかりRange変数にいれてその後のコードを書きましょう。

第1セル.Resize(データ数)

続いてResizeプロパティを使用する方法です。

Dim ws対象シート As Worksheet
Set ws対象シート = ThisWorkbook.Worksheets("○○")

Dim データ数 As Long
データ数 = ws対象シート.AutoFilter.Range.Rows.Count - 1

Dim 売上列データ部 As Range
Set 売上列データ部 = ws対象シート.Range("E4").Resize(データ数)

Resizeはセル範囲を行数・列数指定で取得するプロパティで、
基準セル.Cells(行数, 列数)という記述でセル範囲を取得します。

省略時は元の大きさをそのまま使いますので、
今回の処理では列数は省略して記述できます。

この手法はコードの読みやすさと使いまわしやすさが特長で、

• 「第1セルからデータ数だけ下に伸ばした範囲」というのが読みやすい
• データ数も「フィルター範囲の行数-1」などで簡単に取得できる
• 一度「データ数」という変数を用意すれば各列の取得に使用できる

というメリットがあります。

指定列のデータ部分を取得するにはこのコードが一番使い勝手が良いと思います。

Resizeプロパティ自体がかなり便利なプロパティですので、
このコードとセットでこの機会に覚えてしまいましょう。

Intersect(データ全体, Columns(指定列))

続いてIntersectによる交差範囲取得を用いるパターンです。

Dim ws対象シート As Worksheet
Set ws対象シート = ThisWorkbook.Worksheets("○○")

' データ全体をフィルター範囲から取得
Dim データ範囲全体 As Range
Set データ範囲全体 = ws対象シート.AutoFilter.Range

' フィルター範囲から見出しを除外
Set データ範囲全体 = データ範囲全体.Offset(1)
Set データ範囲全体 = データ範囲全体.Resize(データ範囲全体.Rows.Count - 1)

' 交差範囲を用いてデータ部を取得
Dim 売上列データ部 As Range
Set 売上列データ部 = Intersect(データ範囲全体, ws対象シート.Columns(5))

　
Intersectメソッドは「交差範囲」を取得するメソッドで、
2つ(以上)のセル範囲の重なる部分を取得します。

まずはデータ全体のセル範囲を取得して、
指定列全体との交差範囲を取ることでデータ部分を取得します。

データ全体はフィルター範囲やCurrentRegionなどから取得できますが、
見出し行が不要な場合は1行目を除外する必要があります。

その場合は上記コードのようにResize/Offsetプロパティを用いて、

1. Offsetでフィルターエリア全体をひとつ下にズラす
2. Resizeプロパティでエリアの大きさをひとつ減らす

という手順でセル範囲から一番上の1行を除外します。

この方法も便利に使えることが多い手法ですので覚えておきましょう。

データ全体.Columns(指定列)

最後にColumnsプロパティを使用する方法です。

列全体を取得するおなじみのColumnsプロパティですが、
実はセル範囲から実行することも可能で、その範囲内の第n列を取得できます。

Dim ws対象シート As Worksheet
Set ws対象シート = ThisWorkbook.Worksheets("○○")

' データ全体をフィルター範囲から取得
Dim データ範囲全体 As Range
Set データ範囲全体 = ws対象シート.AutoFilter.Range

' フィルター範囲から見出しを除外
Set データ範囲全体 = データ範囲全体.Offset(1)
Set データ範囲全体 = データ範囲全体.Resize(データ範囲全体.Rows.Count - 1)

' Columnsプロパティを用いてデータ部を取得
Dim 売上列データ部 As Range
Set 売上列データ部 = データ範囲全体.Columns(4)

最後の取得部分以外は、Intersectを使ったコードと全く同じです。

ここでIntersectを使う方法とコードを比べてみてください。

Set 売上列データ部 = Intersect(データ範囲全体, ws対象シート.Columns(5))
Set 売上列データ部 = データ範囲全体.Columns(4)

パッと見Columnsの方が簡単に見えるのですが、
Columns(【4】)とある通り、列番号がシートの列数と異なる点にご注意ください。

セル範囲.Columnsの引数には「そのセル範囲で第何列か」を指定する必要があり、
今回のようにフィルターがB列から始まる場合はE列でも4の指定になります。

これが結構危ない仕様で、オートフィルターは何かの拍子に再設置されるため、
その際A列始まりのフィルターがかかってしまうと不具合になります。

Columnsを使うと場合によっては不安定なコードになるため、
この仕様を理解した上でIntersectメソッドと使い分けてください。

以上で指定列のデータ部分を取得する方法の解説を終わります。

以上4パターンを紹介しましたので、
状況に応じて便利なコードを活用してください。