大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、「変数」を使ってオートフィルタをフィルタする方法について、ご紹介します。フィルタする値の「表示形式を同じ」にする必要があるので、注意です。実務では「変数」を使ってフィルタすることが多いので、「変数」を使ったオートフィルタの操作をマスターしていきましょう。

はじめに

この記事では、VBAで変数を使ってオートフィルタを操作する方法について、ご紹介します。

実務でVBAを使ってオートフィルタを操作する場合は、「変数」や「セルの値」を使ってフィルタする場合が多いです。

オートフィルタを使う場合には、「表の文字」と「フィルタする文字」を完全に一致させる必要があるので、注意です。

VBAを使ってオートフィルタの操作を自動化していきましょう。

では、VBAで「変数」を使ってオートフィルタを操作する方法について、解説していきます。

この記事で紹介すること

  • VBAで変数を使ってオートフィルタを操作する方法

目次から見たい項目へ移動すると便利ですよ。

目次

VBAコードまとめ

オートフィルタを変数を使って操作するVBAコードについて、まとめています。

VBAコードだけを確認したい場合に、ご活用ください。

'【変数でフィルタ】

a = "600" '変数に数値を代入
Range("A1").AutoFilter 2, a '数値をフィルタ

a = "2021/8/1" '変数に日付を代入
Range("A1").AutoFilter 1, a '日付をフィルタ

'【セルの値でフィルタ】
Range("A1").AutoFilter 2, Range("D1") 'セルの値でフィルタ
Range("A1").AutoFilter 2, Format(Range("D1"), "#,##0万人") '表示形式を指定
Range("A1").AutoFilter 1, Format(Range("D1"), "yyyy/m/d") '表示形式を指定
Range("A1").AutoFilter 1, Format(Range("D1"), Range("A2").NumberFormatLocal) '表示形式を指定

'【ワイルドカード】

a = "*府"
Range("A1").AutoFilter 1, a '変数とワイルドカード

Range("A1").AutoFilter 1, "*" & Range("D1") 'セルの値とワイルドカード

'【比較演算子】

a = ">=2021/8/1"
b = "<=2022/1/1"
Range("A1").AutoFilter 1, a, xlAnd, b '変数

Range("A1").AutoFilter 1, ">=" & Range("D1"), xlAnd, "<=" & Range("E1") 'セルの値
    
'【配列】

ReDim a(1 To 3)
a(1) = "東京都"
a(2) = "千葉県"
a(3) = "北海道"
Range("A1").AutoFilter 1, a, xlFilterValues '配列でフィルタ

a = WorksheetFunction.Transpose(Range("A1:A3")) 'セルの値を取得
Range("A5").AutoFilter 1, a, xlFilterValues 'セルの値でフィルタ

では、解説していきます。

オートフィルタで変数を使ってフィルタする

オートフィルタで「変数」を使ってフィルタしてみます。

変数を使ってフィルタ

「変数」を使って数値をフィルタしてみます。

数値をフィルタ

変数に値を代入して、「.AutoFilter」を使うとフィルタできます。

Sub TEST1()
    
    '変数に数値を代入
    a = "600"
    
    '数値をフィルタ
    Range("A1").AutoFilter 2, a
    
End Sub

表を用意します。

表を用意します

では、VBAコードを実行してみます。

数値をフィルタできました

数値をフィルタできました。

日付をフィルタ

日付をフィルタする場合も同じで、変数に日付を入力して「.AutoFilter」でフィルタです。

Sub TEST2()
    
    '変数に日付を代入
    a = "2021/8/1"
    
    '日付をフィルタ
    Range("A1").AutoFilter 1, a
    
End Sub

表を用意します。

表を用意します

では、VBAコードを実行してみます。

日付をフィルタできました

日付をフィルタできました。

セルの値を使ってフィルタ

「セルの値」を使ってフィルタしてみます。

実務ではセルの値を使ってフィルタする、という方法をよく使います。

やり方は、変数を使う方法と一緒です。

ただ、「表の値」と「セルの値」で表示形式を一致させる必要があるので、注意です。

数字をフィルタ

セルに入力した値を使って数値をフィルタしてみます。

Sub TEST3()
    
    'セルの値でフィルタ
    Range("A1").AutoFilter 2, Range("D1")
    
End Sub

セルに数値を入力しておきます。

表を用意します

では、VBAコードを実行してみます。

セルの値を使って数値をフィルタ

「セルの値」を使って数値をフィルタできました。

なんの問題もなさそうです。

表示形式を設定するとちょっと事情が変わってきます。

表示形式が設定されている場合

表に表示形式を設定してみます。

表示形式が設定されている場合

先ほど使ったVBAコードを使ってみます。

Sub TEST4()
    
    'セルの値でフィルタ
    Range("A1").AutoFilter 2, Range("D1") '←表示形式を一致させる必要がある
    
End Sub

では、VBAコードを実行してみます。

表示形式が設定されている場合うまくフィルタできない

「表の値」と「セルの値」で表示形式が違うので、うまくフィルタできていません。

表示形式を設定してフィルタ

セルから取得した値に、「表示形式を設定」してフィルタしてみます。

「Format」を使って表示形式を設定します。

Sub TEST5()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 2, Format(Range("D1"), "#,##0万人")
    
End Sub

では、VBAコードを実行してみます。

表示形式を設定してフィルタできました

表示形式を設定してフィルタできました。

「.NumberFormatLocal」が便利

表示形式を設定する場合は、表の値から「.NumberFormatLocal」を使って表示形式を取得すると便利です。

Sub TEST6()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 2, Format(Range("D1"), Range("B2").NumberFormatLocal)
    
End Sub

では、VBAコードを実行してみます。

「.NumberFormatLocal」を使うと便利です

うまくフィルタできました。

日付をフィルタ

セルに入力した日付を使って、フィルタしてみます。

日付もなかなかクセがあります。

Sub TEST7()
    
    'セルの値でフィルタ
    Range("A1").AutoFilter 1, Range("D1") '表示形式を一致させる必要がある
    
End Sub

セルに日付を入力しておきます。

セルに日付を入力

一見すると、同じ表示形式なので、フィルタできそうです。

では、VBAコードを実行してみます。

セルの値で日付をフィルタできない

セルの値で日付をフィルタできていないです。

セルから値を取得すると、「2021/08/01」となって表示形式が違うので、フィルタできないです。

セルの値は2021/08/01となっていて表示形式が違うのでフィルタできない

セルの値で日付をフィルタするために、表示形式を設定します。

表示形式を設定してフィルタ

表示形式を「"yyyy/m/d"」として、フィルタしてみます。

Sub TEST8()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 1, Format(Range("D1"), "yyyy/m/d")
    
End Sub

では、VBAコードを実行してみます。

表示形式を設定してフィルタできました

表示形式を設定してフィルタできました。

「.NumberFormatLocal」が便利

日付の場合も、「.NumberFormatLocal」を使って、表示形式をフィルタされる値火r亜取得すると、便利です。

Sub TEST9()
    
    'セルの値に表示形式を設定してフィルタ
    Range("A1").AutoFilter 1, Format(Range("D1"), Range("A2").NumberFormatLocal)
    
End Sub

では、VBAコードを実行してみます。

「.NumberFormatLocal」を使ってフィルタできました

「.NumberFormatLocal」を使ってフィルタできました。

こんな感じでピンポイントの値をフィルタする場合は、「表示形式」に注意です。

変数とワイルドカードを使ってフィルタ

変数とワイルドカードを使ってフィルタしてみます。

ワイルドカードの「*」を使ってみます。「〇〇を含む」フィルタをしたい場合に使えます。

変数でフィルタ

ワイルドカード含めて変数に入力して、フィルタします。

Sub TEST10()
    
    '変数にワイルドカードを代入
    a = "*府"
    
    '変数でワイルドカードを使ってフィルタ
    Range("A1").AutoFilter 1, a
    
End Sub

表を用意します。

表を用意します

では、VBAコードを実行してみます。

変数とワイルドカードを使ってフィルタできました

変数とワイルドカードを使ってフィルタできました。

セルの値でフィルタ

セルに入力された値を含む、という条件でフィルタをしてみます。

Sub TEST11()
    
    'セルの値を使ってワイルドカードでフィルタ
    Range("A1").AutoFilter 1, "*" & Range("D1")
    
End Sub

セルに値を入力します。

セルに値を入力します

では、VBAコードを実行してみます。

セルの値とワイルドカードでフィルタできました

「セルの値」と「ワイルドカード」でフィルタできました。

変数と比較演算子を使ってフィルタ

「変数」と「比較演算子」を使ってフィルタしてみます。

変数でフィルタ

「比較演算子」を「変数」に入力してフィルタしてみます。

Sub TEST12()
    
    '日付を変数に入力
    a = ">=2021/8/1"
    b = "<=2022/1/1"
    
    '変数で比較演算子を使ってフィルタ
    Range("A1").AutoFilter 1, a, xlAnd, b
    
End Sub

表を用意します。

表を用意します

では、VBAコードを実行してみます。

変数と比較演算子を使ってフィルタできました

変数と比較演算子を使ってフィルタできました。

セルの値でフィルタ

「セルに入力された値」と「比較演算子」を使ってフィルタしてみます。

Sub TEST13()
    
    'セルの値と比較演算子を使ってフィルタ
    Range("A1").AutoFilter 1, ">=" & Range("D1"), xlAnd, "<=" & Range("E1")
    
End Sub

セルにフィルタしたい値を入力します。

セルにフィルタしたい値を入力します

では、VBAコードを実行してみます。

セルの値と比較演算子でフィルタできました

「セルの値」と比較演算子でフィルタできました。

オートフィルタで配列を使ってフィルタする

オートフィルタで「配列」を使ってフィルタしてみます。

配列を使うと、フィルタしたい値を自由にフィルタできます。

配列を使ってフィルタ

配列を作成しておいて、フィルタしてみます。

Sub TEST14()
    
    '配列に値を入力
    Dim a
    ReDim a(1 To 3)
    a(1) = "東京都"
    a(2) = "千葉県"
    a(3) = "北海道"
    
    '配列でフィルタ
    Range("A1").AutoFilter 1, a, xlFilterValues
    
End Sub

表を用意します。

表を用意

表を用意します

では、VBAコードを実行してみます。

配列でフィルタ

配列でフィルタできました

「配列」でフィルタできました。

セルに入力された値でフィルタ

セルに入力された値でフィルタしてみます。

Sub TEST15()
    
    'セルの値を一次元配列にして変数に格納
    a = WorksheetFunction.Transpose(Range("A1:A3"))
    
    '配列でフィルタ
    Range("A5").AutoFilter 1, a, xlFilterValues
    
End Sub

セルから値を取得すると、2次元配列になっているので、「WorksheetFunction.Transpose」で「一次元配列」に変換しているところが、ポイントです。

セルに値を入力しておきます。

セルに値を入力

セルに値を入力しておきます

では、VBAコードを実行してみます。

セルの値でフィルタ

セルの値でフィルタできました

セルの値でフィルタできました。

おわりに

この記事では、VBAで変数を使ってオートフィルタを操作する方法について、ご紹介しました。

実務でVBAを使ってオートフィルタを操作する場合は、「変数」や「セルの値」を使ってフィルタする場合が多いです。

オートフィルタを使う場合には、「表の文字」と「フィルタする文字」を完全に一致させる必要があるので、注意です。

VBAを使ってオートフィルタの操作を自動化していきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す