【初心者注意】オートフィルタでVBAに不具合が出る原因

2018年6月25日

VBAで、何も考えずにプログラムを組んでいると、今まで普通に動いていたプログラムが突然動かなくなる、という場合があります。

その原因の１つに「オートフィルタ」があります。

実は、オートフィルタをかけることで、今まで動いていたプログラムが、うまく動かなくなってしまうことがあるのです。

この記事の目次

1 最終行の取得
2 データの検索
3 まとめ

最終行の取得

Endはオートフィルタをかけると使えない

データが入力されている最終行を取得する方法を調べると、多くのブログ・ホームページでは、次のような「RangeオブジェクトのEndプロパティ」を使う方法が紹介されています。

Sub 最終行を取得()
  Dim getLastRow As Long
  getLastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  MsgBox getLastRow
End Sub

ところが、この方法では、オートフィルタをかけた場合に最終行を正しく取得することができません。

原因

VBAの「End( ... )」は、エクセルのワークシートで「Ctrl+矢印キー」を押したときと同じ動きをします。

そして、「Ctrl＋矢印キー」での移動は、オートフィルタをかけているかどうかで動きが変わります。

ですから、VBAの「Endプロパティ」を使う操作も、オートフィルタの影響を受けてしまうのです。

解決法

UsedRangeなどを使いましょう。

詳細は【落とし穴に注意】VBAで最終行の行番号を取得する(UsedRangeを使う方法)をご覧ください。

データの検索

Findはオートフィルタをかけると使えない

「指定した値が入力されているセルを探す」方法として、多くのホームページ・ブログでは、次のような「RangeオブジェクトのFindメソッド」を使う方法が紹介されています。

Sub Findで検索()
  Set findRange = Range("A1:C10").Find(what:="東京都", LookAt:=xlWhole)
  
  If findRange Is Nothing Then
    MsgBox "該当ありません"
  Else
    MsgBox findRange.Address & "セルに入力されています"
  End If
End Sub

ところが、この方法では、オートフィルタで非表示になったセルを検索することができません。。

原因

VBAの「Findメソッド」は、エクセルで「Ctrl+F」（検索）をするのと同じ動きをします。

そして、「Ctrl＋F」（検索）は、オートフィルタで非表示になっているセルは検索対象外となります。

ですから、VBAの「Findメソッド」を使う操作も、オートフィルタの影響を受けてしまうのです。

解決法

次のように、地道に１つ１つのセルについて、指定した値と一致しているかどうかを確認しましょう。

Sub for_nextで検索()
  Dim r As Long
  Dim c As Long
  
  For r = 1 To 10          '1行目から10行目
    For c = 1 To 3         '1列目から3列目
      If Cells(r, c).Value = "東京都" Then
        MsgBox Cells(r, c).Address & "セルに入力されています"
        Exit Sub
      End If
    Next
  Next
  
  MsgBox "該当ありません"
End Sub

少し、プログラムが長くなってしまいますが、このようなプログラムを組めば、オートフィルタをかけてもかけなくても表示結果はまったく同じになります。