【Excel VBA】オートフィルタ後に表示されたセルを選択させる
このページで書くこと
Excel VBAでオートフィルタを使用した際、表示された結果が0件かどうかを判別させます。
0件の場合はその旨のポップアップを表示させ、1件以上の場合はその結果の最終行の任意のセルを選択させます。
経緯
以前、少し厄介なデータを手作業で集計した際、
資料Aの1のデータをコピー
→コピーしたデータを資料Bのオートフィルタで検索
→オートフィルタの結果が1件なら作業A、2件以上なら作業B、0件なら作業C
→資料Aの2のでデータをコピー
→……
→資料Aの100のでデータをコピー
→……
といった内容の作業をしなければいけないことがありました。
ただでさえ面倒で仕方がないのに、手作業でのコピーや目視の確認では表示位置のズレなどで間違いも起こりやすく、作業中も不安でした。
なので、少しでも間違いを減らすために
[実行]ボタンクリック
→資料Aで選択中のセルのデータを読み取る
→読み取ったデータを資料Bでオートフィルタ
→オートフィルタの結果が1件なら、その行のデータAをコピー、0件ならその旨のポップアップを出す
というマクロを組みました。
しかし、オートフィルタの結果を数字で認識させるのって難しいんですよね。
結構苦戦しましたので、後学のためにコードを残しておきます。
使用するデータ
以下のようなExcelのサンプルデータを使用します。
「【Excel VBA】オートフィルタを操作する」というページで制作したデータを流用します。
arekoretips.hatenablog.jp
使用するコード
B2セルの「山田勇気」を選択した状態で下記のマクロを動作させると、以下のようにフィルタリングした一覧の最終行のA列(A25セル)を選択しています。
ためしにA1セル(「日付」)を選択した状態でマクロを動作させると、B列に「日付」と入力されたセルはないため「該当なし」のポップアップが表示されます。
Sub AUTOFIL_CEL() ' Dim keyWord As String Dim filterRow As Long ' keyWord = ActiveCell.Value ' '「成績表」シートの1行目のB列を選択中のセルの値でフィルタリング Worksheets("成績表").Rows(1).AutoFilter Field:=2, Criteria1:=keyWord 'フィルタリングされた表の最終表示行を取得 filterRow = Cells(Rows.Count, 1).End(xlUp).Row ' '0件の場合は「該当なし」とメッセージを表示させる If filterRow = 1 Then MsgBox "該当なし" ' 'フィルタリングされた表の最終行を選択する Range("A" & filterRow).Select ' End Sub
解説
まず変数「keyWord」に選択中のセルの値を入れます(ActiveCell.Value)。
別シートや別ブックの値の場合はその旨を指定すれば問題ありません。
次に「 成績表」シートの1行目(Rows(1))のB列(Field:=2)を選択中のセルの値でフィルタリングします。
フィルタリングされた表の最終表示行を取得するために、まずシートの最終行を取得します(Rows.Count)。
シートの最終行というのはつまり、多くのExcelのバージョンでは1,048,576行目(2の20乗)のことです。
そして最終行のA列(Cells(Rows.Count, 1))から、データが入力されているセルまで移動します(End(xlUp))。
※「End(xlUp)」はキーボードのショートカットの[ctrl] + ↑の動きです。
変数「filterRow」には、移動した先の行番号を格納しておきます(.Row)。
もし移動した先が1行目だった場合は、項目行しか存在しないことになりますので、フィルタリングの結果は0件です(If filterRow = 1)。
それ以外の場合は何かしらの検索結果が出ているはずなので、最後に移動した先の行番号のA列のセルをアクティブにします(Range("A" & filterRow).Select)。
備考
シートの最終行(Rows.Count)確認のためにこのようなテストマクロを書きました。
Sub ROWS_TEST() ' MsgBox Rows.Count ' End Sub
結果は以下の通りです。
以上です。