Filtros Avanzados con VBA

 Generalmente utilizamos un formulario o Userform destinado a búsquedas de información.

Pero si buscamos algo sencillo estos 2 ejemplos nos servirán: filtrar tablas de datos dinámicamente, es decir que a medida que escribimos los criterios o los seleccionamos ya vamos obteniendo el filtrado de los datos.

Ejemplo 1:  A medida que se escriben los criterios en un rango destinado a tal fin, la tabla se irá filtrando.

El proceso se ejecuta en el evento Change de la hoja donde se encuentra esta tabla. Y llama a una subrutina llamada 'filtrando' que se encuentra en un módulo.

      Private Sub Worksheet_Change(ByVal Target As Range)
      'se controla lo ingresado en rango de criterios
      If Intersect(Target, Range("$A$2:$C$2")) Is Nothing Then Exit Sub
      On Error Resume Next
      Call filtrando
      End Sub

      Sub filtrando()

      'establecer fin de rango.

      filas = [A6].CurrentRegion.Rows.Count + 3

      'se incrementa en 3 por ser la cantidad de filas por encima del rango

      Range("A6:G" & filas).AdvancedFilter Action:=xlFilterInPlace, _

          CriteriaRange:=Range("A1:C2")

      End Sub

Para quitar el filtrado se borran las celdas de criterio (A2:C2)

Ejemplo 2: Se colocan las celdas de criterios en un rango auxiliar. Se ejecutará a medida que se vayan seleccionando celdas con los criterios elegidos de cada columna. La macro volcará el valor de la selección a las celdas de criterios del Filtro Avanzado para luego ejecutar el filtrado.

En este ejemplo, el proceso se ejecuta al seleccionar una celda de las columnas A:C. Por lo tanto el código se colocará en la hoja a trabajar, evento Selection_Change.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      'solo se ejecuta cuando se seleccione alguna celda en columnas A:C, a partir de la fila 4
      If Target.Column > 3 Or Target.Row < 4 Then Exit Sub
      'según en qué columna se hizo la selección se volcará ese contenido al rango de criterios
      If Target.Column = 1 Then [J2] = Selection.Value
      If Target.Column = 2 Then [K2] = Selection.Value
      If Target.Column = 3 Then [L2] = Selection.Value
      'llama a macro de filtrado
      Call filtrandoAux
      End Sub

    Sub filtrandoAux()

    'x Elsamatilde

    'establecer fin de rango

    filas = [A6].CurrentRegion.Rows.Count

    Range("A3:G" & filas).AdvancedFilter Action:=xlFilterInPlace, _

        CriteriaRange:=Range("J1:L2")

    End Sub

Para quitar el filtrado bastará con seleccionar alguna celda vacía dentro de las columnas A:C para que esas celdas vacías se vuelquen al rango de criterios.

Ahora, si la tabla es muy extensa (y no se visualizan filas vacías) se podría llamar al menú Datos, Filtros, Borrar.  Pero como desde esa opción no se quitarán los criterios y esto puede llevar a cierta confusión, lo mejor será ejecutar la siguiente macro desde el menú Desarrollador:

      Sub quitaFiltros()

      'para quitar los filtros, borrar primero los criterios

       [J2:L2] = ""

      ActiveSheet.ShowAllData

      End Sub

Ver VIDEO 35.

Desde el siguiente enlace se puede descargar el libro con la programación de los ejemplos, tanto del video 35 como del 36:

http://aplicaexcel.com/Blog/FiltroAvanzado.xlsm