大体でIT
大体でIT
Excel VBAで、オートフィルタを設定する方法について、ご紹介します。使うVBAコードは、「.AutoFilter」です。オートフィルタでは「比較演算子」や「ワイルドカード」を使うことができます。VBAでオートフィルタを自動化して実務で使いこなしていきましょう。
この記事では、オートフィルタを設定する方法について、ご紹介します。
オートフィルタをVBAで設定する場合は、「.AutoFilter」を使います。
オートフィルタには、「比較演算子」と「ワイルドカード」、「論理演算子」、「配列を使ったフィルタ」を使うことができます。
「論理演算子」と「配列を使ったフィルタ」については、こちらでまとめていまます。
フィルタする際のポイントは、「表示している文字」でフィルタされるということです。
オートフィルタはかなり便利なExcel機能ですので、VBAで自動化してさらに効率化していきましょう。
では、VBAでオートフィルタを設定する方法について、解説していきます。
オートフィルタを設定するVBAコードについて、まとめています。
VBAコードだけ確認したい場合に、ご活用ください。
'オートフィルタを設定
Range("B2").AutoFilter 2, "東京"
'比較演算子を使う
Range("B2").AutoFilter 2, ">300" '300より大きい
Range("B2").AutoFilter 2, "<300" '300より小さい
Range("B2").AutoFilter 2, ">=300" '300以上
Range("B2").AutoFilter 2, "<=300" '300以下
Range("B2").AutoFilter 1, "<>北海道" '北海道以外
Range("B2").AutoFilter 2, "" '空白
Range("B2").AutoFilter 2, "<>" '空白以外
'ワイルドカードを使う
Range("B2").AutoFilter 1, "*県" '県を含む
Range("B2").AutoFilter 1, "??県" '2文字の県
オートフィルタの設定は、簡単です。1行で書けます。
オートフィルタの設定
Range(表の左上).AutoFilter 列番号, フィルタする値
「表の左上」のセルを指定して、フィルタする「列番号」とフィルタする「値」を入力します。
「.AutoFilter」のイメージ
では、「.AutoFilter」を使って、設定してみます。
「.AutoFilter」で「2列目」を「東京」でフィルタしてみます。
Sub TEST1()
'東京でフィルタ
Range("B2").AutoFilter 2, "東京"
End Sub
「2列目」を「東京」でフィルタ
「空白行」がある場合のオートフィルタの設定をやってみます。
空白行を含んだ表の場合は、「表全体」を指定する必要があります。
空白行がある場合
どうしても空白行が必要という場合は、表全体を指定してあげましょう。
表全体を指定する
空白行がある表でフィルタする
「Range("B2:D11")」というように表全体を指定します。
Sub TEST2()
'表全体を指定してフィルタ
Range("B2:D11").AutoFilter 2, "東京"
End Sub
「空白行」があって「行が可変」の場合を考えてみます。
空白行があって行が可変の場合
表なので、実務で使う際は、行数が変わってしまう場合がほとんどです。
なので、行全体を指定する場合は、「End」を使って最終行を取得しましょう。
「End」を使って行全体を指定する
「Range(左上, 右下)」の「右下」がわからない場合がほとんどです。
表の右下のセルをを「Cells(Rows.Count, "D").End(xlUp)」で取得して、表全体を指定しましょう。
では、「空白行」があって「行が可変」の場合でフィルタしてみます。
「空白行」があって「行が可変」の場合でフィルタ
Sub TEST3()
'Endを使って表全体を指定してフィルタ
Range(Range("B2"), Cells(Rows.Count, "D").End(xlUp)).AutoFilter 2, "東京"
End Sub
「空白行」があって「行が可変」の場合でフィルタできました。
オートフィルタは、「比較演算子」を使ってフィルタすることができます。
- 「">値"」 ：値より大きい
- 「"<値"」 ：値より小さい
- 「">=値"」：値以上
- 「"<=値"」：値以下
- 「"<>値"」：値以外
- 「""」 ：空白
- 「"<>"」 ：空白以外
Sub TEST4()
'「300より大きい」値でフィルタ
Range("B2").AutoFilter 2, ">300"
End Sub
表を用意
「">300"」でフィルタ
Sub TEST5()
'「300より小さい」値でフィルタ
Range("B2").AutoFilter 2, "<300"
End Sub
「"<300"」でフィルタ
Sub TEST6()
'「300以上」でフィルタ
Range("B2").AutoFilter 2, ">=300"
End Sub
「">=300"」でフィルタ
Sub TEST7()
'「300以下」でフィルタ
Range("B2").AutoFilter 2, "<=300"
End Sub
「"<=300"」でフィルタ
Sub TEST8()
'「北海道以外」でフィルタ
Range("B2").AutoFilter 1, "<>北海道"
End Sub
「"<>北海道"」でフィルタ
Sub TEST9()
'「空白」でフィルタ
Range("B2").AutoFilter 2, ""
End Sub
「""」でフィルタ
Sub TEST10()
'「空白以外」でフィルタ
Range("B2").AutoFilter 2, "<>"
End Sub
「"<>"」でフィルタ
オートフィルタでは、「ワイルドカード」を使うことができます。
- 「*」：なんでもいい
- 「?」：なんでもいい。ただし1文字。
「*県」を使って、県を含む値をフィルタしてみます。
Sub TEST11()
'「県を含む」値をフィルタ
Range("B2").AutoFilter 1, "*県"
End Sub
表を用意
「*県」でフィルタ
「??県」を使って、2文字の県をフィルタしてみます。
Sub TEST12()
'「2文字の県」をフィルタ
Range("B2").AutoFilter 1, "??県"
End Sub
「??県」でフィルタ
記号をフィルタする場合は、ちょっと工夫が必要です。
「<」や「=」の比較演算子をフィルタする場合は、「"="」を前につけてフィルタします。
「<」をフィルタする
Sub TEST13()
'「<」をフィルタ
Range("B2").AutoFilter 1, "=<"
End Sub
「=」をフィルタする
Sub TEST14()
'「=」をフィルタ
Range("B2").AutoFilter 1, "=="
End Sub
「?」や「*」のワイルドカードをフィルタするには、前に「"˜"」を付けます。
「?」をフィルタする
Sub TEST15()
'「?」をフィルタ
Range("B2").AutoFilter 1, "~?"
End Sub
「*」をフィルタする
Sub TEST16()
'「*」をフィルタ
Range("B2").AutoFilter 1, "~*"
End Sub
オートフィルタする際の注意は、「表示されている文字」でフィルタされる、ということです。
先ほどの表の値に表示形式を設定して、表示を変更してみます。
表示形式を設定
「"1300"」でフィルタ
Sub TEST17()
'「1300」でフィルタ
Range("B2").AutoFilter 2, "1300" '←うまくフィルタできない
End Sub
「表示されている文字」でフィルタする必要があります。
表示形式まで含めて文字を設定して、フィルタしてみます。
「"1,300万人"」でフィルタ
Sub TEST18()
'「1,300万人」でフィルタ
Range("B2").AutoFilter 2, "1,300万人"
End Sub
こんな感じで、表示している文字でフィルタすると正しくフィルタできます。
「Format」で表示形式を設定
表示形式は、「Format」で設定することもできます。
Sub TEST19()
'表示形式を指定してフィルタ
Range("B2").AutoFilter 2, Format(1300, "#,##0万人")
End Sub
「NumberFormatLocal」を使うと便利
表示形式を調べるのが面倒な場合は、「NumberFormatLocal」を使うと便利です。
Sub TEST20()
'表示形式を指定してフィルタ
Range("B2").AutoFilter 2, Format(1300, Range("C3").NumberFormatLocal)
End Sub
この場合は、「NumberFormatLocal」で「"#,##0万人"」を取得できます。
同じ列には同じ表示形式が設定されている場合がほとんどですので、この方法が使えます。
この記事では、オートフィルタを設定する方法について、ご紹介しました。
オートフィルタをVBAで設定する場合は、「.AutoFilter」を使います。
オートフィルタには、「比較演算子」と「ワイルドカード」、「論理演算子」、「配列を使ったフィルタ」を使うことができます。
「論理演算子」と「配列を使ったフィルタ」については、こちらでまとめていまます。
フィルタする際のポイントは、「表示している文字」でフィルタされるということです。
オートフィルタはかなり便利なExcel機能ですので、VBAで自動化してさらに効率化していきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
