技術tips保管庫

技術tips保管庫

Excel VBAを中心に、仕事で役立つあれこれを記録

【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セル)を選択しています。

フィルタリングした一覧の最終行の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

結果は以下の通りです。

1048576が表示される

以上です。