データが変更されたときにExcelのオートフィルターを自動更新するには？

このコードを以下のように置き換えると、同様にうまくいくようです（少なくともExcel 2010では）：

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

テーブルを扱うときに、これがうまくいかないことがわかりました。フィルタはシート上ではなく、テーブル上にありました。このコードでは、

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
         .AutoFilter.ApplyFilter
    End With
End Sub

がうまくいきました。http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp ](http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp)

私もWorksheet_ChangeイベントをベースにVBA/マクロを使用していますが、私のアプローチは少し違います…。では、まずコードと説明を。

Private Sub Worksheet_Change(ByVal Target As Range)
    ' first remove filter
    ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1        
    ' then apply it again
    ActiveSheet.Range("$L$1:$L$126").AutoFilter Field:=1, Criteria1:="<>0"
End Sub

(Alt+F11キーの組み合わせで開発パネルを表示させ、自動リフレッシュしたいフィルタを含むワークシートにコードを貼り付けます)

この例では、1列（私の場合はL）に単純なフィルタを設定し、データ範囲は1（見出しを含んでいても）から126までの行を想定しています（確実な数値を選択してください）。操作は簡単です: シート上で何かが変更されると、指定された範囲のフィルタが削除されたり適用されたりして、それが更新されます。ここで少し説明が必要なのは、FieldとCriteriaです。

Field は範囲の整数オフセットです。私の場合は1カラムのフィルタしかなく、範囲は1カラム(L)が範囲の最初の1カラム(なので1を値として使っています)で作られています。

Criteria は、データ範囲に適用するフィルタを記述する文字列です。私の例では、L 列が 0 と異なる行のみを表示したいと考えています (したがって、"<>0" を使用しています)。

以上です。Range.AutoFilterメソッドについては、以下を参照してください。 https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

シート名を右クリックし、「コードを表示」を選択し、以下のコードを貼り付けます。貼り付け後、左上の「ファイル」の下にあるExcelアイコンをクリックするか、Alt-F11と入力するとスプレッドシートの表示に戻ります。

これで自動更新が有効になります。マクロをサポートする形式でファイルを保存することを忘れないでください。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.FilterMode = True Then
        With Application
           .EnableEvents = False
           .ScreenUpdating = False
        End With

        With ActiveWorkbook
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True
          Me.AutoFilterMode = False
            .CustomViews("Mine").Show
            .CustomViews("Mine").Delete
        End With

         With Application
           .EnableEvents = True
           .ScreenUpdating = True
        End With
    End If

End Sub
```.

ソリンのコメント:

シート名を右クリックし、「コードの表示」を選択し、以下のコードを貼り付けてください貼り付け後、左上の「ファイル」の下にあるExcelアイコンをクリックするか、Alt-F11を入力してスプレッドシートビューに戻ります。

これで自動更新が有効になります。マクロをサポートした形式でファイルを保存することを忘れないでください。

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

0x1&

ポストを展開しないと、長い答えしか見えません！ ; ; )

using "data, from table"/power query in excel, which gives us option to refresh data when opening file. (also auto sort, and index column (number filtered rows automatically )) This will create result in another sheet. -select data required using mouse (rows and columns) -click on data tab, from table -in the last column, exclude blanks (optional, if you want to to display only filled cells) -add column, index column (optional, if you want to add row number to filtered results) -close and load to to edit again, click on query tab, and then on edit click on design tab in excel, on the arrow below refresh, connection properties, refresh data when opening file. adapted from: https://www.excelcampus.com/tips/sort-drop-down-lists-automatically/ part: 3. Sorting Drop Down Lists Using Power Query you can also copy data from sheet1 if not empty, for example field a1. copy this to a1 field in sheet2: =IF(Sheet1!A1"";Sheet1!A1;"")

すみません、コメントの返信が足りません。(管理者の方、上のコメントにカットしてください。) “私もWorksheet_ChangeイベントをベースにVBA/マクロを使っているのですが、私のやり方は… "で始まる ‘ first remove filter ’ then apply it again がExcel2007+を使っている場合の正解です。しかし、XL03以前のバージョンでは.AutoFilter.ApplyFilterが無効なので、以下のようにしています。

私はそれが最高の材料であることをかなり確信しているので、真の専門家や達人にコードを読んでいただきたいです。おそらく、この回答に対する不可解なダウンボイス数は、人々が下にある良いものを見れば、逆転することができるでしょう。

danicotraは単純化された例を使用しました。実際には、これをより一般的に行うことができます。以下（または他のシートオブジェクト）のActiveSheetと仮定します:

1.オートフィルターの範囲を保存します。.AutoFilter.Filters.Countの列と(.AutoFilter.Range.Count/.AutoFilter.Filters.Count)の行があり、rngAutofilter

1. .AutoFilter.Filters.Count autofilter Itemsの4つのプロパティをmyAutofiltersの配列に集めます。(myAutofiltersはステップ1の行数と列数に合わせて再設定されます)

2. フィルタをオフにしますが、.ShowAllDataを使用してドロップダウンを保持します。保存された配列に従って .On になっている各フィルタ項目について、.AutoFilter.Filters.Count autofilter Items の 4 つのプロパティのうち 3 つをリセットします。ここでも、.Operatorがfalseの場合の "アプリケーション定義エラー "を避けるように注意してください。 AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

これでオートフィルターは、コードを開始する前と同じ範囲で、データの変更のためにオートフィルターが更新された状態で再導入されます。

Public myAutofilters As Variant, rngAutofilter As Range 'Public
Sub SaveAndRestoreAutofilters()
  'This will update the autofilter display to recognize data changes by turning autofilter off and then on, preserving all characteristics
  'Note, XL2007 and later have .autofilter.applyfilter, but not the invaluable XL03 and earlier
  Dim i As Long, iNumAutofilters As Long, iNumActiveAutofilters As Long
  iNumActiveAutofilters = SaveAutoFilterInfo(iNumAutofilters) 'NOTE! Use CALL or assignment to prevent parentheses from forcing ByVal !
  If iNumActiveAutofilters < 1 Then
      Application.StatusBar = "0 ACTIVE filters;" & iNumAutofilters & " autofilters"
      Exit Sub
  End If
  ActiveSheet.ShowAllData

  Rem Here optionally do stuff which can include changing data or toggling autofilter columns

  For i = 1 To iNumAutofilters
      If myAutofilters(i, 1) Then
          If myAutofilters(i, 3) <> 0 Then 'then .Operator is something, so set it and Criteria2, else just Criteria1
              rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2), Operator:=myAutofilters(i, 3), Criteria2:=myAutofilters(i, 4) ', On:=true by rule
          Else
              rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i, 2) ', On:=true by rule (it's R/O anyway)
          End If
          Rem Selection.AutoFilter Field:=i 'How you'd "turn off" only a single column's autofiltering. FYI .On is R/O!
      End If
      'activesheet.autofiltermode=false 'just FYI, how you comprehensively turn off filtering on a sheet (erasing the dropdowns and criteria and filter range!)
  Next i
End Sub
Function SaveAutoFilterInfo(iNumAutofilters As Long) As Long
  Dim i As Long, iRowsAutofiltered As Long
  SaveAutoFilterInfo = 0 'counts the number that are .On, and returns the total
  iNumAutofilters = ActiveSheet.AutoFilter.Range.Columns.Count
  If ActiveSheet.AutoFilter.Filters.Count <> iNumAutofilters Then MsgBox "I can't explain this. All bets are off. Aborting.": Exit function
  ReDim myAutofilters(1 To iNumAutofilters, 4)
  For i = 1 To iNumAutofilters
      myAutofilters(i, 1) = ActiveSheet.AutoFilter.Filters(i).On
      If myAutofilters(i, 1) Then
          SaveAutoFilterInfo = SaveAutoFilterInfo + 1
          myAutofilters(i, 2) = ActiveSheet.AutoFilter.Filters(i).Criteria1
          myAutofilters(i, 3) = ActiveSheet.AutoFilter.Filters(i).Operator
          If myAutofilters(i, 3) <> 0 Then 'then is either xlAnd, xlOr, etc., and there's a second criteria
              myAutofilters(i, 4) = ActiveSheet.AutoFilter.Filters(i).Criteria2
          End If
      End If
  Next i
  iRowsAutofiltered = ActiveSheet.AutoFilter.Range.Count / ActiveSheet.AutoFilter.Range.Columns.Count
  Set rngAutofilter = Cells(ActiveSheet.AutoFilter.Range.Row, ActiveSheet.AutoFilter.Range.Column).Resize(iRowsAutofiltered, iNumAutofilters)
End Function