sábado, mayo 14, 2011

Autofiltro de fechas con macros

Una inesperada lluvia acaba de arruinarme el “bautismo de fuego” de mi nueva bicicleta XC, una Scott Spark 60



que estaba programado para este sábado. Así que dedicaremos esta mañana a un problema que se nos puede presentar a los usuarios de Excel en castellano (o cualquier otra configuración regional fuera del inglés americano).

Supongamos una tabla con fechas y ventas. Podemos mostrar el detalle de ventas de un período con facilidad usando Autofiltro. Esto no es ninguna novedad para ninguno de mis lectores (ni para el 99% de los usuarios de Excel).

Si queremos analizar los datos de la tabla con frecuencia podemos crear una macro para ahorrarnos algunos de los pasos del uso de Autofiltro. La idea es que en una celda pongamos la fecha de inicio del período a analizar, en otra la fecha de cierre del período y apretando hagamos correr una macro que haga la tarea.
Suponiendo que no somos expertos en Vba, empezamos por grabar las acciones que hacemos para aplicar el autofiltro



Al grabar una macro es recomendable darle un nombre significativo, agregar un método abreviado de teclado (preferentemente combinando la tecla Mayúsculas) y poner una descripción de la tarea que cumple la macro.
Supongamos que queremos filtrar la lista para que muestre las ventas del segundo trimestre del 2007 (nótese que ya hemos agregado las celdas para introducir las fechas en la parte superior de la hoja)



EL código resultante es el siguiente

Sub Filtrar_periodo()
'
' Filtrar_periodo Macro
' Filtrar la lista entre dos fechas
'
' Acceso directo: Ctrl+May?s+F
'
    Selection.AutoFilter
    ActiveSheet.Range("$A$6:$F$2088").AutoFilter Field:=1, Criteria1:= _
        ">=01/04/2007", Operator:=xlAnd, Criteria2:="<=30/06/2007"
End Sub



El código que resulta de grabar las acciones presenta dos problemas:
  • El rango de la tabla es fijo, Range("$A$1:$F$2083") 
  • Los criterios de filtrado también están preestablecidos en el código


Para que nuestra macro sea útil tenemos que modificar el código de manera que podamos cambiar las fechas según nuestras necesidades y que el rango de la tabla se adapte cuando esta cambia.


Empecemos por crear dos nombres que se refieran a las celdas B2 (celFechaDe) y B3 (celFechaHasta). La forma má práctica de hacerlo es introduciendo los nombres en el cuadro de Nombres



Ahora editamos nuestro código donde definimos dos variables tipo String (texto) para los criterio de filtrado. 



El código es el siguiente

Sub filtrar_periodo_mejorado()
    Dim strCriterio1 As String
    Dim strCriterio2 As String

    strCriterio1 = ">=" & Range("celFechaDe")
    strCriterio2 = "<=" & Range("celFechaHasta")

    With ActiveSheet
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With

    Range("A6").AutoFilter field:=1, Criteria1:=strCriterio1, _
                            Operator:=xlAnd, Criteria2:=strCriterio2
    
End Sub


Al activar la macro obtenemos el siguiente resultado





Excel nos informa graciosamente que no se han encontrado filas que cumplan con los criterios! 


El problema consiste en que en Vba Excel usa la notación americana de fechas (mes/día/año) de manera que la fecha 01/04/2007 ha sido transformada en 04/01/2007






La segunda fecha no ha sufrido transformación dado que no existe el mes número 30.

La forma de solucionar este problema es pasar las fechas a una variable de tipo Long y con esta variable armar la cadena de texto del criterio.


El código es:


Sub filtrar_periodo_mejorado()
    Dim lFecha1 As Long, lFecha2 As Long


    lFecha1 = Range("celFechaDe")
    lFecha2 = Range("celFechaHasta")

    With ActiveSheet
        If .AutoFilterMode = True Then .AutoFilterMode = False
    End With

    Range("A6").AutoFilter field:=1, Criteria1:=">=" & lFecha1, _
                            Operator:=xlAnd, Criteria2:="<=" & lFecha2
    
End Sub

Como puede verse solo definimos dos variables de tipo Long par alas fechas y la cadena de texto de los criterios la armamos con el operador “ &”


Ahora nuestro modelo funciona sin inconvenientes, sin importar cuáles sean las definiciones regionales del sistema



El archivo con ejemplo y el código puede descargarse aquí .

32 comentarios:

  1. Muy bueno, gracias por tus aportes! Pero me llama la atención cómo es que no se recorre la hoja hacia abajo sino que el encabezado se mantiene mientras que lo filtrado se mueve independiente! Como hiciste eso!? te estaría agradecido que me lo comentaras! De antemano gracias! Excelente tu blog!! Saludos!
    PD: Lastima lo de tu bicicleta pero nunca es tarde para darle su estrenada jeje

    ResponderBorrar
  2. Muy sencillo! Seleccionas la fila adecuada y utilizas Inmovilizar Paneles (en Excel 2007/10 en Vista--Ventana; en Excel 2003 o anterior en el menú Ventana--Inmovilizar Paneles).
    La bicicleta fue estrenada al día siguiente, contra viento y marea (y no poco barro).

    ResponderBorrar
  3. Edicson Rodríguez17 mayo, 2011 16:28

    Excelente comentario, gracias por seguir compartiendo esos detalles que a lo mejor al 90% de los usuarios de Excel no les interesa y no saben de lo que se estan perdiendo y el trabajo que se pueden ahorrar al aplicar todos estos consejo o simplemente lo dinámico que puede ser una hoja de Excel. Nuevamente gracias

    ResponderBorrar
  4. No creo que este sea al local correcto pero no pude encontrar otro; quisiera consultar donde puedo encontrar un listado de temas o si hay un buscador de temas por las páginas del site.
    Saludos
    HBanchieri

    ResponderBorrar
  5. En parte superior del blog hay una nube de etiquetas con los distintos temas. También se puede hacer una búsqueda en la casilla "buscar en el blog".

    ResponderBorrar
  6. Extraordinario blog. Nunca me había dado la oportunidad de comentar nada, hasta hoy. Gracias a este blog me descubrí y me inicié en el mundillo de las macros, que encuentro absolutamente fascinante. Quiero hacer una sugerencia al tema, si en lugar de agregar un botón para correr la macro, se crea una macro por evento ligada al cambio en la celda celFechaHasta y validando que haya valor en ambos rangos, se ahorraría otro clic (y se vería bastante más impresionante ¿No?) Un saludo, estimado autor. Atte Hugo Jiménez

    ResponderBorrar
  7. Si tienen esta macro modificada ligada al evento de cambio en la celda celFechaHasta, me lo podrían compartir, es justo lo que estoy buscando.

    ResponderBorrar
  8. Gaby, en el ejemplo de la nota se puede programar el evento Worksheet_Change con un código como este, por ejemplo


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Union(Target, Range("celFechaHasta")).Address = _
    Range("celFechaHasta").Address Then
    Call filtrar_periodo_mejorado
    End If

    End Sub

    ResponderBorrar
  9. Buenas tardes. Agradezco su ayuda en lo sgte: Tengo un archivo en excel con contraseña y necesito desbloquearlo para poder modificar algunas formular. Traté de hacerlo intruduciendo una macro como vi en un sitio de internet pero el archivo no me permite crear macros ni modificar las existentes. ¿¿como podré hacer para desbloquearlo?? Gracias

    ResponderBorrar
  10. Juan,
    hay muchas aplicaciones que se pueden descargar de la Internet para desbloquear contraseñas. Si se trata de un archivo desarrollado por otra persona lo más indicado sería pedirle a ella la contraseña.

    ResponderBorrar
  11. Hola buen dia Jorge primero felicitarte por tu blog esta genial lo estoy leyendo y tengo un duda o pregunta de como puedo hacer con esto, hice todo los pasos a diferencia del ejemplo que tienes yo solo necesito que busque 1 solo valor dentro de la lista:

    Sub buscar()
    '
    ' buscar Macro
    ' busca Rex


    Dim strCriteria1 As String

    strCriteria1 = Range("Rex")

    With ActiveSheet
    If .AutoFilterMode = True Then .AutoFilterMode = False
    End With


    Range("B2").Select
    Range("B5").AutoFilter Field:=1, Criteria1:="strCriteria1", _
    Operator:=xlAnd
    End Sub

    mi problema especificamente es que por ejemplo de la lista que tengo para buscar son codigos

    A82WM7510
    A82WM7051
    A82WM7053
    A82FJ01

    y lo que quiero es que la funcion que haga es que busque por ejemplo A82 y me de los resultados de toda la lista que contengan esas primeras letras, que modificacion tendria que hacer porque solo me da resultados si pongo el nombre completo si no, no me arroja resultados porque me pide que sea exacto el nombre y yo quiere que tenga la funcion de CONTIENE.

    agradezco de ante mano tu respuesta

    ResponderBorrar
  12. Te sugiero que grabes la acción (con la grabaadora de macros) las acciones de filtrar con el criterio CONTIENE y te fijes luego en el editor de Vba cuál es la sintaxis.

    ResponderBorrar
  13. Antonio Sánchez12 abril, 2012 02:31

    Hola muy buena esta macro pero tengo un problema para que me lea unas fechas generadas con un calendario en un userform ya que no las reconoce como tal. He probado a volcarlas a una celdas o a unos textbox, pero no las reconoce como fechas. En cambio las escribo manualmente en unas celdas y la macro funciona perfectamente.Te agradeceria si me pudieras ayudar..........

    ResponderBorrar
  14. Hola Antonio,
    tendría que ver cómo lo estás haciendo, pero si pasas la fecha del Calendar a una celda, ésta pasa como fecha.
    De todas maneras, puedes forzar el tipo de valor en Vba usando la función CDate.

    ResponderBorrar
  15. hola tengo una duda, estaba viendo el archivo y ese filtro funciona bastante bien me gusto mucho mas que otros filtros, pero me gustaria poder quitarles esas flechitas que tiene en los encabezados es posible?

    ResponderBorrar
  16. haras uno que haga que copie el resultado en otra hoja?

    ResponderBorrar
  17. Quitar las flechas significa remover el autofiltro. Si quieres filtrar una tabla y mantener los resultados después de quitar el autofiltro tienes que usar Filtro Avanzado. En mi blog hay varias notas sobre el tema (incluyendo copiar el resultado a otras hoja)

    ResponderBorrar
  18. EXCELENTE TE FELICITO

    ResponderBorrar
  19. exelente... gracias, tu si sabes...

    ResponderBorrar
  20. Hola Jorge,
    Buenísimo el aporte, pero tengo un problema quiero filtara los datos desde la celda A6 Para abajo, pero cuando ejecuto la macro me filtra los datos de la celda A2, Me podrias ayudar Gracias,,,,,,

    ResponderBorrar
  21. Pablo, cuando aplicas Autofiltro a una tabla, siempre la primer fila es considerada la fila de los encabezamientos, excepto que selecciones primero todo el rango, por ejemplo A6:C6, y luego apliques el Autofiltro.

    ResponderBorrar
  22. Se que este foro es algo antiguo, pero necesito una solución a mi problema. Estoy copiando tal cual el código de filtrar la fecha a un formato que tengo, el codigo corre sin problema pero en vez de hacer el filtro por las fechas hace el filtro por numeros. Es decir si mi filtro fue del 01/02/2016 al 05/02/2016, en el filtro del excel aparece como si el filtro fuera "42401" al "42405", por lo que no me arroja ningún valor de la tabla. :(

    ResponderBorrar
  23. Analista, las fechas son números (fijate en este post). 42401 es el número que representa la fecha 01/02/2016.
    Te sugiero que me envíes tu archivo para que pueda revisarlo.

    ResponderBorrar
  24. Hola Jorge, el archivo que compartes funciona a la perfeccion , pero al ser utilizada la macro en otro archivo arroja el error2No coiciden los tipos" en la parte : lFecha1 = Range("celFechaDe") debido a que sale ese error? Gracias de antemano

    ResponderBorrar
  25. Hola, parece ser que el valor a que se refiere el nombre "celFechaDe" no es una fecha válida. Tendría que ver tu archivo.

    ResponderBorrar
  26. Necesito que en una tabla dinámica me filtre un rango de fechas, osea colocar las fechas en una celda (fecha inicial) y en otra celda (fecha final) y las filtre pero en la tabla dinámica.

    ResponderBorrar
  27. Para filtrar la tabla de fecha a fecha puedes usar Segmentación de datos (slicer) o los autofiltros de los campos de la tabla. Pero si tiene que ser a partir de valores, tendrás que usar macros.

    ResponderBorrar
  28. Jorge Dunkelman, he intentado hacer esa acción en una tabla dinámica de MO 2013 y no he podido encontrar la forma, incluso intente aplicar tu ejemplo de la macro para ver que pasaba y no me surtió efecto, sera que hay que cambiar algún dato en la macro o ¿definitivamente no se podrá realizar esta acción en tabla dinámica?...en espera de tus comentarios

    ResponderBorrar
  29. Las tablas dinámicas tienen su propio método de Filtro por lo que esta macro no se aplica a ellas.
    Tendrías que modificar la macro de manera que se refiera a la tabla dinámica y usar los métodos apropiados.

    ResponderBorrar
  30. Excelente aporte, me estuve quebrando la cabeza por horas debido a que se me presentaba este error en el formato de fechas, este fué el único sitio que encontré que pudo resolver mis dudas

    Gracias!

    ResponderBorrar
  31. Jorge:
    Muchas gracias por esta macro........el dia de hoy me ha servido para un archivo que se necesitaba trabajar con estos requerimientos...
    Muchas gracias

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.