オートフィルターがかかっている列を検索する機能を作った

列が何十・何百とあるExcelシートでどこにオートフィルターがあるか迷ったことはないだろうか？

文字通り、「あれ、この辺りだと思ったけど」と右往左往したことはないだろう？

私はある。

現在進行系で悩まされている。

しかもまだ１・2ヶ月はこのシートとお付き合いしなくちゃいけないらしい。

右往左往している時間がもったいない。

そんな時はVBAで作るに限る。

完成品挙動

サクッと作ってみた。

※データはなんちゃって個人情報より

起動するといきなりリストボックスだけのフォームが開いて、

・フィルターの列名

・シート名

・セルアドレス

だけが表示されている

こいつをクリックすると該当の場所に飛ぶようになっている。

コード

標準モジュール ※不具合のため公開時より一部コード修正いたしました

Function AutoFilterSearch()
     Dim i As Long
     Dim n As Long
     Dim myVar() As Variant
          
     With ActiveSheet.AutoFilter
          If ActiveSheet.AutoFilterMode Then
               ReDim myVar(.Filters.Count)
               For i = 1 To .Filters.Count
                    If .Filters(i).On Then
                         myVar(n) = .Range.Cells(i).Value & "," & .Parent.Name & "," & .Range.Cells(i).Address(False, False)
                         n = n + 1
                    End If
               Next
          End If
     End With
     
     If n = 0 Then
          myVar = Array()
     Else
          ReDim Preserve myVar(n - 1)
     End If
     AutoFilterSearch = myVar
End Function

ユーザーフォーム

Private Sub UserForm_Initialize()
     Dim myVar As Variant
     myVar = AutoFilterSearch
     
     Dim i As Long
     For i = 0 To UBound(myVar)
          Me.ListBox1.AddItem myVar(i)
     Next
End Sub

Private Sub ListBox1_Click()
    Dim Adr As String
    Dim myWS As Worksheet
    Dim myList As Variant
    
    myList = Split(ListBox1.List(ListBox1.ListIndex), ",")
    Set myWS = Worksheets(myList(1))
    Adr = myList(2)

    myWS.Activate
    Range(Adr).Activate
End Sub

AutoFilterSearch関数

Autofilter.Filters.countでオートフィルターがかかっている列数を取得できます。

For文でAutofilterがかかっている各列を順番にチェックしていきます。

Autofilter.Filters(i).Onで該当列にオートフィルターがかかっているかどうかを判定。

オートフィルターがかかっている列の先頭セルのRangeオブジェクトを取得するには

AutoFilter.Rangeプロパティを使用します。

AutoFilter.Rangeプロパティはオートフィルターの対象となっているセル範囲を取得できます。

取得したRangeオブジェクトに対してCells(i)プロパティを使用することで先頭セルを特定しています。

イメージはこのような感じです。

この関数は「オートフィルターの列名,シート名,セルアドレス」という文字列を各要素に含めた配列を返します。

オートフィルターがかかっていな場合やフィルターの絞り込みがされていない場合は空配列を返します。

ユーザーフォーム

ユーザーフォーム起動時に上記のAutoFilterSearch関数を使用してオートフィルターがかかっている列の情報を受け取ってListboxに表示しています。

あとはListboxのクリックイベントにセル選択の機能を追加して完成です。

まとめ

シンプル・イズ・ベストで単機能に仕上げてみました。

来週以降実戦投入してみます。

楽しみです。