jueves, 10 de marzo de 2016

VBA: Macro de un Filtro Avanzado condicionado según celda validada.

Recientemente me han preguntado varias veces sobre la misma cuestión:
¿Cómo realizar filtros avanzados tomando rangos de criterios variables, elegidos según una celda validada?

Y esto es precisamente, mediante una sencilla macro, lo que vamos a realizar hoy.
Partimos del siguiente planteamiento:

VBA: Macro de un Filtro Avanzado condicionado según celda validada.


Importante el rango de trabajo, la 'base de datos' está en A1:D21.
Los rangos de criterios opcionales, a los que hemos asignado Nombres Definidos son:
Filtro_1 =Hoja1!$I$2:$J$4
Filtro_2 =Hoja1!$I$8:$I$11
Filtro_3 =Hoja1!$I$15:$K$16

Y en la celda F1 de la hoja hemos creado una Celda validada tipo Lista, la cual nos despliega tres opciones: Filtro_1;Filtro_2;Filtro_3, siendo éstos, precisamente, los Nombres definidos asociados a los rangos de criterios a aplicar a nuestra base de datos.


La idea, por tanto, es clara. Queremos que según seleccionemos algún valor en la celda F1 se aplique el filtro correspondiente.
Un dato importante para el tercero de los criterios, en el que hemos incluido un filtro de fechas, es que las he construido de la siguiente forma:
=">="&FECHA(2016;1;1)
="<="&FECHA(2016;3;31) el motivo es por que de otra forma la programación no las entendería.


Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de la ventana de código de la hoja de trabajo desde el editor de VB; empleando el evento _Change (que nos asegura la ejecución del procedimiento al variar el dato de la celda F1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F1")) Is Nothing Then
    'La celda activa SÍ cruza con las celdas del rango
    'eliminamos cualquier filtro existente en el rango filtrado
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    'recuperamos lo escrito en la celda F1
    'para luego tratarlo como nombre definido
    NombreDefinido = "" & Range("F1").Value & ""
    'aplicamos el filtro avanzado... con el rango de criterios
    'el Nombre seleccionado en F1
    Range("A1:D21").AdvancedFilter Action:=xlFilterInPlace, _
            CriteriaRange:=Hoja1.Range(NombreDefinido), _
            Unique:=False
End If
End Sub



Podemos probar variando el dato de F1, y comprobaremos cómo se aplica el filtro correspondiente...

16 comentarios:

  1. Que debo hacer para que esta macro se ejecute

    ResponderEliminar
    Respuestas
    1. Hola claudio,
      que tal estás?, un placer saludarte igualmente

      Solo debes hacer lo que se indica:
      insertamos el siguiente código dentro de la ventana de código de la hoja de trabajo desde el editor de VB
      al ser un evento se ejecutará sola cuando cambie algo en la hoja...
      UN cordial saludo

      Eliminar
    2. hola ismael romero, por favor tu ayuda con este caso, deseo hacer un filtro a traves de una macro y de la filtracion que se haga, reemplazar la columna siguiente por otro dato, es decir todo el rango filtrado por otro texto.

      slds
      cristian rodriguez

      Eliminar
    3. Hola Cristian
      prueba a seleccionar ese rango y usar el método .Replace

      Sería lo más simple
      Saludos

      Eliminar
  2. Buen dia,

    Gracias, otra consulta si quiero filtrar una tabla dinámica desde una celda pero desde otra hoja que debo hacer.

    ResponderEliminar
    Respuestas
    1. Hola Claudio,
      aplicar el valor de la celda como criterio del filtro en la tabla dinámica...
      Busca en la categoría de macros del blog, encontrarás algún ejemplo similar a lo que describes
      Saludos

      Eliminar
  3. Gracias,
    Tengo otra consulta tengo una tabla dinamica y quiero filtrarla por el cambio de dos celdas es posible con una macro.
    tengo esto
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("F1")) Is Nothing Then

    'En base al campo Region de la Tabla dinámica1:
    With PivotTables("Tabla_dinámica2").PivotFields("ORIGEN")

    'Limpiar todos los filtros
    .ClearAllFilters

    'Filtrar por el valor de la celda F1
    On Error Resume Next
    .CurrentPage = Range("F1").Value

    End With
    End If
    end sub

    Loc ual me sirve para una celda pero dos celdas como lo haria.

    Agradezco sus comentarios

    ResponderEliminar
    Respuestas
    1. Hola Claudio
      eso es algo más elaborado
      puedes echar un vistazo a este post
      https://excelforo.blogspot.com/2016/03/vba-lanzar-macro-cuando-cambia-el-valor.html

      Te dará la pista...
      Saludos

      Eliminar
    2. Gracias,

      Pero lo que quiero si es posible en que al modificar la fila F1 Y F2 a la vez haga el filtrado con el codigo que envie.

      Saludos.

      Eliminar
    3. Hola!
      a la vez no podrás cambiar las celdas correspondientes...
      seguro cambias una primero y después otra.
      Añade en el evento que se describe un control sobre una u otra

      Saludos

      Eliminar
  4. Hola Ismael, mi duda es si se podrian filtar datos como si fueran rangos, me explico, Si por ejemplo me dan un valor en una celda, digamos 34567 y en la columna M Y L esta el inicio de este rango y el Final, por lo que, tendria que poder devolver la informacion de este rango la fila que contenga el inicio del rango y el final. Esto es posible? Te doy un ejemplo mas sencillo, en la celda B5 se tiene el valor de 5 y en la columna c y D representan el inicio del rango y el final, C2 seria 1 y D2 10 por lo que B5 si pertenece a este rango, de antemano gracias y saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      con filtros avanzados (con o sin fórmulas) parece posible
      =Y(B5>C2;B5<D2)
      esa fórmula, dentro del rango de criterios, te filtraría lo registros que cumplieran

      Revisa la categoría del blog de filtros, y verás algún ejemplo

      Saludos

      Eliminar
  5. Hola buena tarde; tengo filtro según el valor de una celda en tres tablas dinámicas ubicadas en la misma hoja, pero cuando en una o varias de ellas no encuentra un valor asociado al filtro me arroja todos los datos de la tabla. Hay alguna forma de que en este caso pueda arrojar la tabla vacía o en blanco?

    ResponderEliminar
    Respuestas
    1. Hola!
      entiendo estás gestionándolo con una macro que aplica filtro según el valor de la celda...
      Se me ocurre que antes de aplicar el filtro verifique si existe el valor o no en esa TD... y condicionar la aplicación del filtro a su existencia...

      Espero te oriente la idea.
      Saludos

      Eliminar
  6. Hola,
    quiero filtrar por dos campos, pero que este en uno O en otro.
    (OR)
    es posible?
    gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      en tu rango de criterios debajo del campo1 pones el criterio y debajo del campo2 en OTRA FILA el mismo criterio

      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.