【VBA】オートフィルタでの日付データ抽出改善例

こんにちは、Ryoです。
オートフィルタを使用して日付データを抽出することは多々あると思いますが、Excelのバージョンや日付の表示形式の組み合わせによっては抽出上手くいかなかったり、できないことがあります。そのような場合は日付データを一度シリアル値に変換、抽出することで改善できますので、今回はその内容について書いていきます。

1.サンプル概要
2.サンプルコード
3.まとめ

1.サンプル概要

サンプルとして以下の表があるとします。抽出したい日付をセルB1に入力しますが敢えて表示形式を変えています。その入力された日付データを表内からオートフィルタを使用して抽出するものです。

実際にサンプルコードを実行すると目に見えない部分なので、途中で止めた状態を以下に表示し説明していきます。

冒頭にも書きましたが、日付データをシリアル値に変えることで抽出結果を改善するものなので、先ずは日付データをシリアル値に変えています。

次にオートフィルタを実行する際に「抽出日」に入力されたデータを読み、シリアル値に変換して抽出します。ちなみに2020年2月3日をシリアル値に変換すると「43864」になので、処理としては問題なく抽出できています。

後はシリアル値となっている日付データを元の表示形式に戻します。

また、オートフィルタを解除（すべて選択）すれば、非表示となっているデータも全て元の日付表示形式に戻してあります。

以上がサンプル概要になります。

2.サンプルコード

Sub Sample1()

  Dim dayFormat As String
  Dim Target As Range
 
  'セルA3を基点に表全体を選択し、その中で
  'A列のデータのみ変数として取得する
  Set Target = Range("A3").CurrentRegion
  With Target
    Set Target = .Resize(.Rows.Count - 1, 1).Offset(1)
  End With
 
  'データ最初のセル（本サンプルではA4）書式を変数に代入
  dayFormat = Target.Cells(4, 1).NumberFormatLocal
  
  'データの書式を「標準」にする
  '標準指定することで日付データはシリアル値となる
  Target.NumberFormatLocal = "標準"
 
  'オートフィルタでセルB1に入力された日付データを抽出
  '（抽出日付データはシリアル値に変換）
  Range("A3").AutoFilter Field:=1, _
  Criteria1:=CDbl(DateValue(Range("B1")))
       
  'シリアル値に変換されたデータを全て当初の
  '日付表示形式に戻す
  For i = 1 To Target.Rows.Count
    Cells(i + 3, 1).NumberFormatLocal = dayFormat
  Next i

End Sub

セルの表示形式を取得／設定するにはNumberFormatLocalプロパティを使用します。

構文：オブジェクト.NumberFormatLocal = expression

本サンプルではNumberFormatLocalプロパティを利用することで、日付データをシリアル値に変換したり、元の形式に戻したりしています。

先ずはセルA3を基点とした表から日付データが入力されているA列のデータを取得します。

Set Target = Range(“A3”).CurrentRegion
With Target
Set Target = .Resize(.Rows.Count – 1, 1).Offset(1)
End With

データ取得後、後で表示形式を元に戻す為に入力済みデータから現在の表示形式を取得して変数に代入しておきます。

dayFormat = Target.Cells(4, 1).NumberFormatLocal

次に最初に取り込んだ日付データの書式を「標準」指定することで、データが「シリアル値」となります。サンプル概要で説明した日付データが全てシリアル値になっているのは、この行の処理実行後を表しています。

Target.NumberFormatLocal = “標準”

シリアル値に変換されたデータに対し、抽出したい日付データ（セルB1) でオートフィルタを実行します。当然ながら抽出したい日付データもシリアル値に変えています。

Range(“A3”).AutoFilter Field:=1, _
Criteria1:=CDbl(DateValue(Range(“B1”)))

後は日付データを全て元の表示形式に戻しますので、A列の全データに対しNumberFormatLocal に代入しておいた変数dayFormatを指定することで当初の表示形式となります。