arigirisu

【Excel VBA】AutoFilter 日付値で抽出する

—

by

arigirisu
in

AutoFilterメソッドで抽出する場合、日付値を持つフィールドは要注意です。

AutoFilterメソッドの引数

引数省略可否用途
Field必須抽出対象のフィルター番号を指定する
Criteria1　抽出条件1を指定する
Operator　抽出種類をXlAutoFilterOperator列挙の定数で指定する
Criteia2　抽出条件2を指定する

XlAutoFilterOperator列挙の定数

定数値用途
xlAnd1Criteria1とCriteria2のAnd条件で抽出
xlOr2Criteria1とCriteria2のOr条件で抽出

日付値を持つ表

普通に日付値で抽出してみる

たとえば、下記のコードで試してみます。上の表の場合、日付値を持つフィールドの番号は 2 です。
日付が2022/1/3のレコードを抽出してみます。

Sub 日付値で抽出()
   Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:= "2022/1/3"
End Sub

＜注意＞条件の日付値は、field2(C列)に表示されている表示形式の文字列で指定します。

InputBoxから入力された日付値で抽出する

問題なく抽出できました。しかし、普通はユーザーに日付を指定してもらうはずです。

Sub 日付で抽出2()
    Dim trgtDay As Date
    trgtDay = Application.InputBox( _
        prompt:="日付を入力してください", _
        Title:="日付指定")
    '抽出する
    Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:=trgtDay
End Sub

実行すると次のようになります。

＜注意＞条件の日付値は、field2(C列)に表示されている表示形式での文字列で指定します。

うまく抽出できません。
これは、変数trgtDateの型をDate型にしたからです。
String型に直すと正しく抽出します。

Sub 日付で抽出3()
    Dim trgtDay As String
    trgtDay = Application.InputBox( _
        prompt:="日付を入力してください", _
        Title:="日付指定")
    '抽出する
    Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:=trgtDay
End Sub

指定セルに入力された日付値で抽出する

指定セル（例ではセルG2）に入力された日付値で抽出する場合のコードはこんな感じでしょうか。

Sub 日付で抽出4()
    Dim trgtDay As String
    trgtDay = Range("G2").Value
    '抽出する
    Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:=trgtDay
End Sub

＜注意１＞これでうまく抽出できますが、「セルG2」の書式設定を「文字列」にしておくことを忘れないで下さい。
＜注意２＞「セルG2」には field2(C列)に表示されている表示形式での文字列を入力しないといけません。

「期間」で抽出する

日付値から日付値までの期間に該当するレコードを抽出する場合は、Criteria1, Operator, Criteria2の3つの引数を使います。
下のコードは、セルG2の日付値からセルI2の日付値までの期間に該当するレコードを抽出します。
＜重要＞ところでExcelは期間計算するとき、表示形式に関わらずシリアル値ベースで計算を行い、抽出を行うようです。したがって、セルG2やセルI2の書式設定は何でもOK。field2(C列)の表示形式も気にする必要がありません。
VBAの方の変数の型もString型でもDate型でもOKです。

Sub 日付で抽出5()
    Dim startDay As Date
    Dim endDay As Date
    startDay = Range("G2").Value
    endDay = Range("I2").Value
    '抽出する
    Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:=">=" & startDay, _
        Operator:=xlAnd, _
        Criteria2:="<=" & endDay
End Sub

＜注意＞9行目と11行目の条件式に注意して下さい。
「Criteria1 :=」「Criteia2 :=」の次に「 “>=” & startDay」、「 “<=” & endDay 」と書きます。

日付値の表示形式を気にしないで抽出する

「期間」で抽出する場合、日付値の表示形式を気にしないでいいのはわかりました。
冒頭の事例のように「特定の日付値」で抽出する場合でも、表示形式を気にしないで済むといいですよね。
実は簡単です。期間で抽出するように見せかければいいんです。

指定セルに入力された日付値で抽出する （表示形式無視バージョン）

Sub 日付で抽出6()
    Dim startDay As Date
    Dim endDay As Date
    startDay = Range("G2").Value
    endDay = startDay
    '抽出する
    Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:=">=" & startDay, _
        Operator:=xlAnd, _
        Criteria2:="<=" & endDay
End Sub

5行目を　endDay = startDay　に変えただけです。

InputBoxから入力された日付値で抽出する （ 表示形式無視バージョン） 

Sub 日付で抽出7()
    Dim startDay As Date
    Dim endDay As Date
    startDay = Application.InputBox( _
        prompt:="日付を入力してください", _
        Title:="日付指定")
    endDay = startDay
    '抽出する
    Range("B2").CurrentRegion.AutoFilter _
        field:=2, _
        Criteria1:=">=" & startDay, _
        Operator:=xlAnd, _
        Criteria2:="<=" & endDay
End Sub

同様に7行目を　endDay = startDay　にしています。

まとめ

日付値で抽出を行う場合、表示形式を気にしないで済むようするには、マクロ上は「期間」で抽出すればいいということです。
Excelをだましているみたいで楽しいですね。
ユーザーが日付を入力しさえすれば表示形式を無視できるというのが最大のメリットです。

Comments

コメントを残す