オートフィルターのデータ部分を取得する

オートフィルターのデータ部分をRangeオブジェクトに取得する方法を解説します。

AutoFilter.Rangeで簡単に取得できますが見出し部分もついてきてしまうため、
Offset&Resizeプロパティを利用して見出しを除外して取得します。

さらに指定列のデータ部分を取得したい場合は、
Intersectメソッドを使用して取得できます。

オートフィルターのデータ部分を取得する

ソースコード

Dim フィルターデータ部 As Range
Set フィルターデータ部 = Worksheets("○○").AutoFilter.Range
Set フィルターデータ部 = フィルターデータ部.Offset(1)
Set フィルターデータ部 = フィルターデータ部.Resize(フィルターデータ部.Rows.Count - 1)

Debug.Print フィルターデータ部.Address ' B4:E9を取得

解説

オートフィルターの範囲全体を取得する方法は簡単で、
「対象Worksheet.AutoFilter.Range」で取得できます。

問題はこれだと見出しがついてきてしまう点で、
ここから見出しを除外するにはOffset+Resizeを使用します。
　

• Offsetプロパティは「セル範囲全体を指定数だけ移動する」プロパティ
• Resizeプロパティは「セル範囲のサイズを再設定する」プロパティ

ですので、Offsetでフィルターエリア全体をひとつ下にズラし、
Resizeプロパティでエリアの大きさをひとつ減らすことで目的の範囲を取得できます。

Resizeは「拡大縮小」ではなく「サイズの再設定」を行うプロパティのため、
「Resize(-1)」ではなく「Resize(元範囲.Rows.Count - 1)」
とする必要があることに注意してください。

また、このコードは一発で書くとかなり読みづらくなります。

Worksheets("○○").AutoFilter.Range.Offset(1).Resize(Worksheets("○○").AutoFilter.Range.Rows.Count - 1)

　
しっかりRange変数を用意すると格段に読みやすくなりますので、
こういった変数の用意は怠らないようにしましょう。

Dim フィルターデータ部 As Range
Set フィルターデータ部 = Worksheets("○○").AutoFilter.Range
Set フィルターデータ部 = フィルターデータ部.Offset(1)
Set フィルターデータ部 = フィルターデータ部.Resize(フィルターデータ部.Rows.Count - 1)

指定列のデータ部分を取得する

続いて指定列のデータ部分を取得するコードを解説します。

オートフィルター全体のデータ部分を取得できていれば、
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))

Debug.Print 売上列データ部.Address ' E4:E9を取得

解説

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

これを活用することで、指定列のデータ部分を取得することができます。

ちなみにセル範囲から列を切り出にはColumnsプロパティも使えます。

Set 売上列データ部 = Intersect(フィルターデータ部, ws対象シート.Columns(5))
' ↓ Columnsを使って書き替え
Set 売上列データ部 = フィルターデータ部.Columns(4)

　
パッと見Columnsの方が簡単に見えるのですが注意点があり、
Columns(【4】)とある通り、シートの列番号とは異なる指定になります。

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

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

Columnsを使うと不安定なコードになりがちなため、
この処理にはIntersectメソッドを使用しておきましょう。

汎用関数化

この処理をよく行う方は汎用関数にして持っておくと便利です。

' 汎用関数の使用例
みかんの売上 = WorksheetFunction.SumIf _
    (Get指定列のデータ部分(ws対象シート, 2), "みかん" _
   , Get指定列のデータ部分(ws対象シート, 5))

' フィルターのデータ部分の取得
Function Getフィルターデータ部分(指定シート As Worksheet) As Range
    If 指定シート.AutoFilterMode = False Then Exit Function
    
    Dim フィルターデータ部 As Range
    Set フィルターデータ部 = 指定シート.AutoFilter.Range
    
    If フィルターデータ部.Rows.Count = 1 Then Exit Function
    
    Set フィルターデータ部 = フィルターデータ部.Offset(1)
    Set フィルターデータ部 = フィルターデータ部.Resize(フィルターデータ部.Rows.Count - 1)
    
    Set Getフィルターデータ部分 = フィルターデータ部

End Function

' 指定列のデータ部分
Function Get指定列のデータ部分(指定シート As Worksheet, 指定列 As Long) As Range
    Set Get指定列のデータ部分 = Intersect(Getフィルターデータ部分(指定シート) _
                                                      , 指定シート.Columns(指定列))
End Function

関数の中身は今回のコードをほぼそのまま書いているだけですが、
使用例の通り、非常に便利な関数になっていることが分かります。

このように「簡単だけど書くのが面倒なコード」は、
関数化が簡単な割に、汎用関数にするメリットが大きいです。

汎用関数集を作っている方は、ぜひそのメンバーに加えてあげてください。

汎用関数集の作り方・使い方についてはこちらをどうぞ。

www.limecode.jp