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

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

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

私はある。

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

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

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

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

完成品挙動

サクッと作ってみた。
f:id:ExcelLover:20200418214228g:plain
※データはなんちゃって個人情報より

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

・フィルターの列名

・シート名

・セルアドレス

だけが表示されている

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

コード

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

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