Excel VBA オートフィルターで特定の列の複数項目を除外する

はじめに

VBAのオートフィルターで、特定の列の複数項目を含む条件には、配列を使えばよい。
では、逆に特定の列の複数項目を除く条件の場合には、どうすればよいか考えたい。

マクロ作成の経緯

CSVデータのうち、A列の複数項目を含むデータのみを残し、他のデータは削除したい。
この他のデータをまとめて削除する方法はないかと考えたのがきっかけだった。

複数項目を含むという条件でオートフィルターを使い、結果を別シートにコピーすればよいのだが、他のデータだけをまとめて削除できたら、もっとシンプルになるのではと考えた。

結果、別シートにコピーする方がシンプルだったが、複数項目を除く条件を設定したい場合には使えるのではと思う。

作成したマクロの概要

オートフィルターに複数項目を除く条件を直接設定することはできない。
作業セルを使う方法をヒントに、除外したい項目に印をつける方法で作成することにした。

動作手順

1. 別シートにある指定した項目と、項目ラベルからデータ一覧の列番号を取得する
2. 指定した項目・列番号のオートフィルターを実行する
3. 指定した項目のデータのA列セルを塗りつぶす
4. A列の塗りつぶされていないセルをオートフィルターで抽出する
   （結果、指定した項目を除くデータが表示される）

注意点

• A列に始めからセルの塗りつぶしデータがある場合は使えない
  (塗りつぶしを初期化してしまうため、元々の塗りつぶしが消えてしまう)

完成したマクロ

前提として、データ一覧とは別の「除外条件」シートに条件を記載。
データ一覧のあるシートをアクティブにして、マクロを実行する。

「除外条件」シートのイメージ

データ一覧のあるシートのイメージ

サンプルとして、総務省の統計に用いる標準地域コードのCSV形式の全国版を使用した。

作成したコード

AutofilterExcludeMultiple

Public Sub AutofilterExcludeMultiple()
Dim TargetColumnNo As Long
Dim ItemCount As Long
Dim TargetItemArray() As String
Dim i As Long

    '項目列番号・除外対象項目を取得
    With ThisWorkbook.Sheets("除外条件")
    
        TargetColumnNo = WorksheetFunction.Match(.Range("A2").Value, ActiveSheet.Rows(1), 0)       
        
        ItemCount = .Range(.Range("C2"), .Range("C2").End(xlDown)).Count
        ReDim TargetItemArray(ItemCount - 1) 
        
        For i = 0 To ItemCount - 1    
            TargetItemArray(i) = .Cells(i + 2, "C").Value       
        Next i 
        
    End With

    With ActiveSheet
        'オートフィルターが設定されている場合には解除
        If Not .AutoFilter Is Nothing Then .Range("A1").AutoFilter 
        .Columns(1).Interior.Color = xlNone
        
        .Range("A1").AutoFilter Field:=TargetColumnNo, Criteria1:=TargetItemArray, Operator:=xlFilterValues
        .Range(.Range("A2"), .Range("A2").End(xlDown)).Interior.Color = vbYellow
        
        .ShowAllData
        .Range("A1").AutoFilter Field:=1, Operator:=xlFilterNoFill
            
    End With

End Sub

サンプルファイル保存先：

参考

• 複雑な条件（複数除外等）のオートフィルター（AutoFilter）｜VBA技術解説