¿Cómo actualizar automáticamente un filtro automático de Excel cuando se modifican los datos?

Intercambiar el código con esto parece hacer el truco también (al menos en Excel 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

Encontré que cuando trabajaba con tablas, esto no funcionaba. El filtro no estaba en la hoja sino en la tabla. este código hizo el truco

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

Encontré la información aquí: http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp

Yo también uso un VBA/Macro basado en el evento Worksheet_Change, pero mi enfoque es ligeramente diferente… Ok, primero el código y luego las explicaciones:

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

(Usa la combinación de teclas Alt+F11 para que aparezca el panel de desarrollo y pega el código en la hoja de trabajo que contiene el filtro que quieres que se auto-refresque).

En mi ejemplo estoy suponiendo que tengo un filtro simple en una sola columna (L en mi caso) y que mi rango de datos está en las filas desde 1 (aunque pueda contener encabezamiento) hasta 126 (elige un número lo suficientemente grande para estar seguro). El funcionamiento es sencillo: cuando se cambia algo en mi hoja, el filtro en el rango especificado se elimina/se aplica de nuevo para que se actualice. Lo que necesita un poco de explicación aquí son Campo y Criterios.

El Campo es un desplazamiento entero del rango. En mi caso, sólo tengo un filtro de una sola columna y el rango está hecho por una sola columna (L) que es la primera del rango (por lo tanto uso 1 como valor).

El Criterio es una cadena que describe el filtro a aplicar al rango de datos. En mi ejemplo, quiero mostrar sólo las filas en las que la columna L sea distinta de 0 (por eso he utilizado “<>0”).

Eso es todo. Para más referencias sobre el método Range.AutoFilter, ver: https://msdn.microsoft.com/en-us/library/office/ff193884.aspx

Haz clic con el botón derecho del ratón en el nombre de tu hoja, elige “Ver código” y pega el código que aparece a continuación. Después de pegarlo, haz clic en el icono de Excel que está debajo de “Archivo” en la parte superior izquierda, o escribe Alt-F11, para volver a la vista de la hoja de cálculo.

Esto activará la actualización automática. No olvide guardar el archivo en un formato con soporte para macros lie .xlsm.

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

Sólo para consolidar la(s) respuesta(s):

Sorin dice:

Haz clic con el botón derecho del ratón en el nombre de tu hoja, elige “Ver código” y pega el código que aparece a continuación. Después de pegarlo, haz clic en el icono de Excel que aparece debajo de “Archivo” en la parte superior izquierda, o escribe Alt-F11, para volver a la vista de la hoja de cálculo.

Esto activará la actualización automática. No olvides guardar el archivo en un formato con soporte para macros lie .xlsm.

Y Chris usó este código (que acabo de hacer en 2010):

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.AutoFilter.ApplyFilter

End Sub

¡Si no expandes el post, sólo verás la respuesta larga! ;)

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;"")

Lo siento, no hay suficientes representantes para comentar. (Administradores, siéntanse libres de cortar esto en un comentario arriba.) La respuesta del usuario “danicotra” que comienza con “Yo también uso un VBA/Macro basado en el evento Worksheet_Change, pero mi enfoque…” con ‘ primero quitar el filtro ’ luego aplicarlo de nuevo es la solución correcta cuando se usa Excel 2007+. Sin embargo .AutoFilter.ApplyFilter no es válido en XL03 y anteriores por lo que muestro la forma de abajo.

Ruego que los verdaderos expertos y gurús lean el código porque estoy bastante seguro de que es material de primera. Tal vez la inexplicable cuenta de downvote en esta respuesta pueda revertirse cuando la gente vea el buen material que se hace abajo.

danicotra utilizó un ejemplo simplificado. En realidad, usted puede hacer esto de manera más general. Supongamos Con ActiveSheet para lo siguiente (o algún otro objeto de hoja):

1. Guarda el rango del autofiltro. Tiene columnas de .Autofiltro.Cuenta, y filas de (.Autofiltro.Cuenta/.Autofiltro.Cuenta), guardadas en rngAutofiltro

2. Recoge en un array myAutofilters cada una de las 4 propiedades de cada uno de los autofiltros .AutoFilter.Filters.Count, teniendo cuidado de que no se produzcan “errores definidos por la aplicación” cuando .On u .Operator sean falsos. (myAutofilters se reDim’d al número de filas y columnas del paso 1)

3. Desactive el filtro pero conserve los desplegables con .ShowAllData

4. Para cada elemento del filtro que estaba .On según su matriz guardada, restablezca 3 de las 4 propiedades de cada uno de los elementos del autofiltro .AutoFilters.Count. De nuevo, tenga cuidado de evitar “errores definidos por la aplicación” cuando .Operator es falso, así que para cada elemento “i”, rngAutofilter.AutoFilter Field:=i, Criteria1:=myAutofilters(i,2) o rngAutofilter. AutoFilter Field:=i, Criteria1:=myAutofilters(i,2), Operator:=myAutofilters(i,3), Criteria2:=myAutofilters(i,4)

Ahora el autofiltro se restablecerá, sobre el mismo rango que había antes de que su código comenzara, pero con el autofiltro actualizado para los cambios en los datos.

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