【VBA】オートフィルタの操作を徹底解説【実務で使えるレベルになります】
Excel VBAで、オートフィルタの操作を徹底解説します。オートフィルタの設定から解除、色フィルタ、日付、並び替え、フィルタ結果の操作までを網羅的に解説します。オートフィルタはかなり便利な機能ですので、VBAでの操作方法をマスターして実務で使いこなしていきましょう。
はじめに
この記事では、オートフィルタの操作を、網羅的にご紹介します。
オートフィルタの設定や解除、色フィルタ、日付フィルタ、並び替え、フィルタの結果の操作方法など、実務で使える内容です。
オートフィルタは、よく使う便利な機能ですので、マスターして実務で使いこなしていきましょう。
では、VBAでオートフィルタを操作する方法について、解説していきます。
この記事を読むメリット
- VBAでオートフィルタを操作する方法が理解できます
目次
オートフィルタでフィルタする
オートフィルタでフィルタしてみます。
フィルタしてみる
オートフィルタでフィルタするには、「.AutoFilter」を使います。
「.AutoFilter」を使う
Range(表の左上).AutoFilter 列番号, フィルタする値
Rangeで表の左上を設定して、「列番号」と「フィルタする値」を設定します。
「.AutoFilter」を使って、フィルタしてみます。
フィルタしてみる
「2列目」で「"東京"」をフィルタしてみます。
Sub TEST1()
'東京でフィルタ
Range("B2").AutoFilter 2, "東京"
End Sub
では、VBAコードを実行してみます。
「"東京"」をフィルタできました。
空白行がある場合
実務でよくあるのが、表の中に「空白行」があるパターンです。
空白行がある場合
空白行がある場合は、表全体を指定してフィルタします。
VBAコードは、次のように「Range("B2:D11")」のように表全体を指定します。
Sub TEST2()
'表全体を指定してフィルタ
Range("B2:D11").AutoFilter 2, "東京"
End Sub
では、実行してみます。
「空白行」がある場合でフィルタできました。
空白行があって行が可変の場合
実務で使う表は、行数が可変なものがほとんどです。
空白行があって行が可変の場合は、自動で表範囲を取得して、フィルタできるようにします。
「End」を使えば、最終行を取得することができます。
「Range」で「左上」と「右下」のセル範囲を指定して、表全体を指定します。
VBAコードは、次のようになります。
Sub TEST3()
'Endを使って表全体を指定してフィルタ
Range(Range("B2"), Cells(Rows.Count, "D").End(xlUp)).AutoFilter 2, "東京"
End Sub
では、VBAコードを実行してみます。
「"東京"」をフィルタできました。
フィルタする際の注意
フィルタする際の注意は、「表示されている文字でフィルタされる」ということです。
表に表示形式を設定して、見た目を変えてみます。
表示形式が設定されている場合
入力されている値は、「数値」ですけど、見た目が変わります。
「"1300"」でフィルタしてみます。
「"1300"」ではフィルタできない
Sub TEST4()
'「1300」でフィルタ
Range("B2").AutoFilter 2, "1300" '←うまくフィルタできない
End Sub
実行してみます。
フィルタ結果は「なし」となりました。
「"1300"」ではフィルタできないです。
表示されている「"1,300万人"」でフィルタする必要があります。
「"1,300万人"」でフィルタできる
Sub TEST5()
'「1,300万人」でフィルタ
Range("B2").AutoFilter 2, "1,300万人"
End Sub
では、VBAコードを実行してみます。
「"1,300万人"」でフィルタできました。
こんな感じで、ピンポイントでフィルタする場合は、「表示されている文字」でフィルタする必要があります。
表示形式は「Format」や「NumberFormatLocal」を使うと便利
表示形式を設定するに際には、「Format」や「NumberFormatLocal」を使うと簡単に設定できます。
表示形式を「Format」や「NumberFormatLocal」で設定する方法について、詳細はこちらでまとめています。
「<」や「=」、「*」、「?」をフィルタする
比較演算子の「<」や「=」をフィルタする際は、前に「"="」を付けてフィルタします。
ワイルドカードの「*」や「?」をフィルタする場合は、前に「"˜"」をつけてフィルタします。
「<」や「=」、「*」、「?」をフィルタする方法について、詳細はこちらでまとめています。
比較演算子を使ってフィルタ
比較演算子を使ってフィルタしてみます。
オートフィルタでは、「比較演算子」が使えます。
比較演算子の種類は、次のとおりです。
比較演算子の種類
- 「">値"」 ：値より大きい
- 「"<値"」 ：値より小さい
- 「">=値"」：値以上
- 「"<=値"」：値以下
- 「"<>値"」：値以外
- 「""」 ：空白
- 「"<>"」 ：空白以外
比較演算子を使ってフィルタしてみます。
「300より大きい」値をフィルタ
「300より大きい」値をフィルタする場合は、「">300"」を使ってフィルタします。
Sub TEST6()
'「300より大きい」値でフィルタ
Range("B2").AutoFilter 2, ">300"
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「300より大きい」値をフィルタできました。
「北海道以外」をフィルタ
「北海道以外」をフィルタしてみます。
「"<>北海道"」と入力するとできます。
Sub TEST7()
'「北海道以外」でフィルタ
Range("B2").AutoFilter 1, "<>北海道"
End Sub
実行してみます。
「北海道以外」をフィルタできました。
「空白以外」をフィルタ
「空白以外」をフィルタしてみます。
「"<>"」と入力することで、空白以外をフィルタできます。
Sub TEST8()
'「空白以外」でフィルタ
Range("B2").AutoFilter 2, "<>"
End Sub
実行してみます。
「空白以外」をフィルタできました。
「空白」をフィルタする方法や「その他のフィルタ」
比較演算子を使えば紹介したもののほかにも、フィルタする方法はあります。
空白をフィルタする方法や、「その他のフィルタ」については、こちらでまとめています。
ワイルドカードを使ってフィルタ
「ワイルドカード」を使ってフィルタすることもできます。
ワイルドカードは、2つの種類があります。
ワイルドカードの種類
- 「*」：なんでもいい
- 「?」：なんでもいい。ただし1文字。
何文字でもいい場合は「*」を使って、1文字を指定したい場合は「?」を使います。
では、ワイルドカードでフィルタしてみます。
「県を含む」値をフィルタ
「県を含む」値をフィルタしてみます。
「"*県"」というようにすると、県を含む値をフィルタできます。
Sub TEST9()
'「県を含む」値をフィルタ
Range("B2").AutoFilter 1, "*県"
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「県を含む」値をフィルタできました。
「2文字の県」をフィルタ
「2文字の県」をフィルタしてみます。
今度は、2文字という条件があるので、「?」を使います。
引数には、「"??県"」とすると「2文字の県」をフィルタできます。
Sub TEST10()
'「2文字の県」をフィルタ
Range("B2").AutoFilter 1, "??県"
End Sub
では、実行してみます。
「2文字の県」をフィルタできました。
論理演算子を使ってフィルタ
「論理演算子」を使うと、2つ条件を指定して、フィルタすることができます。
論理演算子には、2種類あります。
論理演算子の種類
- xlAnd：AND条件で絞り込み
- xlOr：OR条件で絞り込み
では、論理演算子を使って、フィルタしてみます。
「300以上」かつ「700以下」でフィルタ
「300以上」かつ「700以下」でフィルタしてみます。
条件が「かつ」なので、「xlAnd」を使います。
Sub TEST11()
'「xlAnd」条件でフィルタ
Range("B2").AutoFilter 2, ">=300", xlAnd, "<=700"
End Sub
表を用意しておきます。
では、実行してみます。
「300以上」かつ「700以下」でフィルタできました。
「300より小さい」もしくは「700より大きい」でフィルタ
次は、「300より小さい」もしくは「700より大きい」でフィルタしてみます。
フィルタ条件が「もしくは」なので、「xlOr」を使います。
Sub TEST12()
'「xlOr」条件でフィルタ
Range("B2").AutoFilter 2, "<300", xlOr, ">700"
End Sub
実行してみます。
「300より小さい」もしくは「700より大きい」でフィルタできました。
論理演算子を使うと、フィルタできる幅がグッと広がります。
複数でフィルタ
複数でフィルタしてみます。
3つ以上の値をフィルタしたい場合に使えます。
「東京都」「北海道」「福岡県」でフィルタ
複数でフィルタするには、「配列」を使います。
3つ目の引数に、「xlFilterValues」を使うのがポイントです。
Sub TEST13()
'3つの値でフィルタ
Range("B2").AutoFilter 1, Array("東京都", "北海道", "福岡県"), xlFilterValues
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「東京都」「北海道」「福岡県」でフィルタできました。
配列でフィルタする際のポイント
配列を使う場合は、「=」でフィルタできるものに限ります。
なので、「比較演算子」や「論理演算子」、「ワイルドカード」は使えません。
配列を使う際のポイントは、こちらでまとめています。
変数を使ってフィルタ
変数を使ってフィルタしてみます。
フィルタする場合は、「変数」を使ってフィルタすると便利です。
変数を使ってフィルタ
変数を使ってフィルタしてみます。
Sub TEST14()
'日付を変数に入力
a = ">=2021/8/1"
b = "<=2022/1/1"
'変数で比較演算子を使ってフィルタ
Range("A1").AutoFilter 1, a, xlAnd, b
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「変数」を使ってフィルタできました。
セルの値でフィルタ
セルの値でフィルタしてみます。
「セルに入力した値」を使ってフィルタする方法も、実務ではよく使います。
Sub TEST15()
'セルの値と比較演算子を使ってフィルタ
Range("A1").AutoFilter 1, ">=" & Range("D1"), xlAnd, "<=" & Range("E1")
End Sub
セルに値を入力しておきます。
では、VBAコードを実行してみます。
セルの値でフィルタできました。
セルに入力された「3つ以上の値」でフィルタ
「3つ以上の値」をセルに入力してフィルタすることもできます。
セルに入力された値を「配列」に入力してフィルタする、という方法です。
セルに入力された「3つ以上の値」でフィルタする方法について、こちらでまとめています。
色フィルタを使う
オートフィルタでは、「色フィルタ」を使ってフィルタすることができます。
オートフィルタの矢印ボタンを押すとでてくるやつです。
オートフィルタの「色フィルタ」
オートフィルタの「色フィルタ」で、フィルタしてみます。
背景色でフィルタ
背景色でフィルタしてみます。
「第2引数」に「色」を指定して、「第3引数」に「xlFilterCellColor」を入力します。
Sub TEST16()
'「背景色」を「黄色」でフィルタ
Range("B2").AutoFilter 1, RGB(255, 255, 0), xlFilterCellColor
End Sub
表を用意しておきます。
では、実行してみます。
背景色でフィルタできました。
塗りつぶしなしでフィルタ
塗りつぶしなしでフィルタしてみます。
「第2引数」は「空白」として、「第3引数」に「xlFilterNoFill」を入力します。
Sub TEST17()
'塗りつぶしなしでフィルタ
Range("B2").AutoFilter 1, , xlFilterNoFill
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「塗りつぶしなし」でフィルタできました。
文字色でフィルタ
「文字色」でフィルタしてみます。
文字色でフィルタするには、「第2引数」に「色」を入力して、「第3引数」に「xlFilterFontColor」を入力します。
Sub TEST18()
'「文字色」を「赤」でフィルタ
Range("B2").AutoFilter 1, RGB(255, 0, 0), xlFilterFontColor
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「文字色」でフィルタできました。
自動でフィルタ
文字色を「自動」でフィルタしてみます。
「自動」はデフォルトの色で、色的には「黒色」になります。
「第2引数」を「空白」にして、「第3引数」に「xlFilterAutomaticFontColor」を入力します。
Sub TEST19()
'「文字色」を「自動」でフィルタ
Range("B2").AutoFilter 1, , xlFilterAutomaticFontColor
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
文字色を「自動」でフィルタできました。
「特定の色以外」でフィルタ
色フィルタでフィルタできるのは、1種類だけです。
特定の色以外や複数の色で、フィルタする場合は、「作業列」が必要です。
「特定の色以外」でフィルタする方法について、こちらでまとめています。
日付フィルタを使う
「日付フィルタ」を使って、フィルタしてみます。
「日付フィルタ」は、次のようなものです。
オートフィルタの「日付フィルタ」
全期間の日付フィルタもできます。
では、日付フィルタを使ってフィルタしてみます。
「8月」でフィルタ
「8月」でフィルタしてみます。
「第2引数」に「28」を入力して、「第3引数」に「11」を入力します。
Sub TEST20()
Range("A1").AutoFilter 1, 28, 11 '8月
End Sub
では、VBAコードを実行してみます。
「8月」でフィルタできました。
ただ、日付フィルタはあまり使わないです。
日付を「作成」して、比較演算子や論理演算子でフィルタする方が簡単で、応用が利きます。
日付フィルタの引数一覧と日付をフィルタするポイント
「日付フィルタの引数一覧」と「日付をフィルタするポイント」については、こちらでまとめています。
作業列を使ってフィルタ
標準のオートフィルタ機能でフィルタ出来ないフィルタの場合は、「作業列」をうまく使いましょう。
「作業列」を使ってフィルタしてみます。
「AとDとG」以外でフィルタ
「AとDとG」以外でフィルタというのをやってみます。
「AとDとG」以外の場合に、作業列に印を入力します。
Sub TEST21()
For i = 2 To 13
'商品がA、D、G以外の場合
If Cells(i, "A") <> "A" And Cells(i, "A") <> "D" And Cells(i, "A") <> "G" Then
Cells(i, "C") = "〇" '作業列に印をつける
End If
Next
'作業列でフィルタ
Range("A1").AutoFilter 3, "〇"
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
作業列を使って「AとDとG」以外でフィルタできました。
複雑なフィルタが必要な場合は、「作業列」を使いましょう。
作業列を使う際のポイント
作業列を使う際は、「Endで最終行を取得する」して、「配列を使って高速化する」すると便利に使うことができます。
作業列を使うポイントについて、詳細はこちらにまとめています。
オートフィルタの解除
オートフィルタの解除をしてみます。
2種類の解除があります。
- オートフィルタの解除
- オートフィルタのフィルタの解除
という感じです。
では、それぞれやってみます。
オートフィルタの解除
オートフィルタの解除をしてみます。
オートフィルタを解除
「.AutoFilter」を使います。
Sub TEST22()
'オートフィルタを解除
Range("B2").AutoFilter
End Sub
フィルタしておきます。
では、VBAコードを実行してみます。
オートフィルタを解除できました。
「.AutoFilter」の動作
「.AotuFilter」は、「設定」と「解除」を交互に繰り返します。
「.AutoFilter」の使い方の詳細については、こちらでまとめています。
オートフィルタのフィルタを解除
もう一つの解除で、オートフィルタのフィルタを解除する、というのをやってみます。
オートフィルタのフィルタを解除
使うのは、「.ShowAllData」です。
オートフィルタは設定された状態にして、フィルタのみを解除します。
Sub TEST23()
'オートフィルタのフィルタを解除
ActiveSheet.ShowAllData
End Sub
フィルタしておきます。
では、VBAコードを実行してみます。
オートフィルタの「フィルタを解除」できました。
「ShowAllData」の使い方
「ShowAllData」はフィルタしていない場合に実行するとエラーとなってしまいます。
実務でエラーを出さないように「ShowAllData」を使う方法について、こちらでまとめています。
実務で使う場合は最初と最後に解除
実務で使う場合は最初と最後に、オートフィルタの解除をするといいです。
最初と最後に「.AutoFilter」を使う
VBAコードはこんな感じになります。
Sub TEST24()
'オートフィルタを解除
Range("B2").AutoFilter
'オートフィルタ操作をする
Range("B2").AutoFilter 3, ">=1000"
'オートフィルタを解除
Range("B2").AutoFilter
End Sub
では、VBAコードを実行してみます。
という感じで、オートフィルタの解除を使います。
オートフィルタの解除を実務で使う場合
他にも、実務で使う場合、最後にフィルタのみを解除するという方法も考えられます。
オートフィルタの解除を実務で使う場合について、こちらでまとめています。
オートフィルタで昇順と降順にソート
オートフィルタで昇順と降順にソートしてみます。
オートフィルタの「.Sort」を使う方法とセルを並び替える「.Sort」があります。
オートフィルタの「.Sort」はわかりづらいのでセルを並び替える「.Sort」を使った方が簡単です。
オートフィルタを使う
オートフィルタの「.Sort」を使う方法です。
オートフィルタのソートはわかりづらい
オートフィルタの「.Sort」は、こんな感じになります。
Sub TEST25()
'オートフィルタを設定
Range("A1").AutoFilter
With ActiveSheet.AutoFilter.Sort
With .SortFields
.Clear 'ソートキーをクリア
'昇順にソート
.Add Key:=Range("C1"), Order:=xlAscending
End With
.Apply '設定を適用する
End With
End Sub
表を用意しておきます。
表を用意
では、VBAコードを実行してみます。
「オートフィルタ」で昇順にソート
「オートフィルタ」で昇順にソートできました。
「Sort」の方が簡単
並び替えをする場合は、セルを並び替えする「.Sort」の方が簡単です。
「Sort」で昇順にソート
「Sort」で昇順にソートするVBAコードです。
Sub TEST26()
'昇順にソート
Range("A1").Sort Key1:=Range("C1"), order1:=xlAscending, Header:=xlYes
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
「Sort」で昇順にソートできました。
「Sort」で降順にソート
降順に並び替える場合は、「order1:=xlDescending」を使います。
セルを並び替える方法については、こちらでまとめています。
オートフィルタの結果を操作
オートフィルタの結果を操作です。
フィルタした結果を、「コピー」や「削除」、「連番を振る」などをいろいろできます。
フィルタした結果を操作する場合は、「フィルタ結果を判定」して、操作するとエラー回避することができます。
オートフィルタの結果を判定
最初に、オートフィルタの結果を判定する方法をみてみます。
フィルタ結果があるかを判定
使うのは、「Subtotal関数」です。
「Subtotal関数」のカウント機能を使うと、簡単にフィルタ結果を判定できます。
Sub TEST27()
'「フィルタ結果の数」が「1より大きい」場合
If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
MsgBox "データあり"
Else
MsgBox "データなし"
End If
End Sub
フィルタしておきます。
では、VBAコードを実行してみます。
「データあり」となりました。
フィルタ結果があるかを判定できています。
フィルタした結果を操作する場合は、このように「判定」して操作します。
詳細な解説
フィルタ結果を判定する方法について、詳細はこちらでまとめています。
オートフィルタの結果をコピー
オートフィルタの結果を「コピー」してみます。
フィルタ結果をコピーするVBAコードは、次のようになります。
フィルタ結果をコピー
Sub TEST28()
'東京をフィルタ
Range("A1").AutoFilter 2, "東京"
'見出し以外のフィルタ結果をセルにコピー
With Range("A1").CurrentRegion
.Resize(.Rows.Count - 1).Offset(1, 0).Copy Range("E1")
End With
'オートフィルタを解除
Range("A1").AutoFilter
End Sub
このVBAコードに、「フィルタ結果を判定」するVBAコードを追加します。
フィルタ結果を判定してコピー
Sub TEST29()
'東京をフィルタ
Range("A1").AutoFilter 2, "東京"
'フィルタ結果がある場合だけ
If WorksheetFunction.Subtotal(3, Range("A:A")) > 0 Then
'見出しを除くフィルタ結果をセルにコピー
With Range("A1").CurrentRegion
.Resize(.Rows.Count - 1).Offset(1, 0).Copy Range("E1")
End With
End If
'オートフィルタ解除
Range("A1").AutoFilter
End Sub
では、実際に実行してみます。
表を用意しておきます。
では、VBAコードを実行してみます。
フィルタ結果をコピーできました。
フィルタ結果がない場合は、コピーはされません。
詳細な解説
「.Resize」や「.Offset」、「.Copy」の使い方について、詳細はこちらでまとめています。
オートフィルタの結果を削除
次は、オートフィルタの結果を「削除」してみます。
フィルタ結果を削除するVBAコードは、次のようになります。
フィルタ結果を削除
Sub TEST30()
'東京をフィルタ
Range("A1").AutoFilter 2, "東京"
Application.DisplayAlerts = False '表示をオフにする
'見出しを除いてフィルタ結果を削除
With Range("A1").CurrentRegion
.Resize(.Rows.Count - 1).Offset(1, 0).Delete
End With
Application.DisplayAlerts = True '表示をオンにする
'オートフィルタを解除
Range("A1").AutoFilter
End Sub
先ほどと同じように、「フィルタ結果を判定」するVBAコードを追加します。
フィルタ結果を判定して削除
Sub TEST31()
'東京でフィルタ
Range("A1").AutoFilter 2, "東京"
'フィルタ結果がある場合
If WorksheetFunction.Subtotal(3, Range("A:A")) > 0 Then
Application.DisplayAlerts = False '表示をオフにする
'見出しを除くフィルタ結果を削除
With Range("A1").CurrentRegion
.Resize(.Rows.Count - 1).Offset(1, 0).Delete
End With
Application.DisplayAlerts = True '表示をオンにする
End If
'オートフィルタを解除
Range("A1").AutoFilter
End Sub
では、実際に実行してみます。
表を用意しておきます。
では、VBAコードを実行してみます。
フィルタ結果を削除できました。
フィルタ結果がない場合は、削除されずそのままとなります。
詳細な解説
「.Resize」や「.Offset」、「.Delete」の使い方について、詳細はこちらでまとめています。
オートフィルタの結果に連番を入力
オートフィルタの結果に「連番」を入力してみます。
フィルタ結果に連番を入力
可視セルのみ操作したいので、「SpecialCells(xlCellTypeVisible)」を使います。
Sub TEST32()
'「東京」でフィルタ
Range("A1").AutoFilter 2, "東京"
'フィルタ結果がある場合
If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
With Range("A1").CurrentRegion
'見出しを除く表示している行をループ
For Each a In .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows
i = i + 1
'行の1行4列目に連番を入力
a.Cells(1, 4) = i
Next
End With
End If
'フィルタを解除
Range("A1").AutoFilter
End Sub
表を用意しておきます。
では、VBAコードを実行してみます。
フィルタ結果に「連番」を入力できました。
詳細な解説
「.Resize」や「.Offset」、「.SpecialCells(xlCellTypeVisible)」などの考え方は、こちらで、まとめています。
おわりに
この記事では、オートフィルタの操作を、網羅的にご紹介しました。
オートフィルタの設定や解除、色フィルタ、日付フィルタ、並び替え、フィルタの結果の操作方法など、実務で使える内容です。
オートフィルタは、よく使う便利な機能ですので、マスターして実務で使いこなしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す