jueves, 15 de octubre de 2009

Macro de un filtro avanzado en Excel.

En una entrada anterior de este blog sobre los filtros avanzados, nos introdujimos en esta herramienta; hoy, a partir de éste crearemos una Macro con la que ejecutaremos este Filtro avanzado, para posteriormente asignarle un botón. Tendrá sentido esta creación de macros cuando el usuario final de nuestras bases de datos no tenga conocimientos suficientes de esta herramienta avanzada.
Realizaremos una configuración sencilla, sin entrar en definir rangos dinámicos que complicarían la explicación y el desarrollo de la programación de la macro; por tanto, emplearemos el Asistente para Macros para luego realizar alguna modificación directamente sobre el código desde el Editor de Visual Basic(Alt+F11).
Desde luego no considero que tenga suficientes conocimientos de programación en Visual Basic ni tan siquiera para darme la categoría de principiante, tan sólo me dedico a observar en qué forma redacta el código el Asistente, para tras analizarlo poder cambiar determinados parámetros o líneas de la programación.
Dicho esto, para nuestro ejemplo partimos de la siguiente base de datos:


Antes de continuar generaremos o asignaremos nombre a los siguientes rangos:
campos = $A$1:$I$1 recoge la cabecera de la base de datos,
Criterios = $K$1:$M$3 el rango donde se encuentran los criterios,
Datos = $A$1:$I$13 el rango de la base de datos.
Crear estos nombres es importante para poder personalizar posteriormente nuestra Macro, puesto que al fijar rangos nombrados, simplemente cambiando directamente en la hoja de cálculo los criterios de filtro y pulsando el botón de ejecución, se filtrarán los registros de la base de datos que cumplan estos criterios.
Supongamos que nuestras condiciones siempre estarán vinculados a tres campos como máximo, y una doble condición al menos sobre una de estas, es decir, que nuestro rango de criterios para el filtro avanzado corresponde a un rango fijo; en nuestro ejemplo a Criterios=$K$1:$M$3:


Con esta limitación, de momento, podemos iniciar la secuencia de movimientos grabados mediante el Asistente para macros (Menú Programador > Grupo Código > Opción Grabar Macro):


Observamos en el video adjunto que hemos añadido dos botones, de los controles de formulario, para poder ejecutar las dos macros creadas de manera sencilla. Uno de ellos nos permite el filtro avanzado y el otro un borrado del resultado del filtrado.
Los códigos de la programación son los siguientes:

83 comentarios:

  1. Quiero hacer lo mismo pero filtrando horas:
    ej: >10:00 y <=18:00

    pero no lo consigo. Formatos de celda en hora puestos.

    ResponderEliminar
  2. Hola,
    el problema de trabajar con horas y minutos, es que Excel aunque lo muestre con el formato 00:00, realmente tiene un número decimal (entre 0 y 1) debajo de este. Por ejemplo, las 10:00 es 0,42 y las 18:00 es 0,75.
    http://excelforo.blogspot.com/2009/06/convertir-tiempos.html
    Esto significa que para que te funcionen los filtros avanzados o cualquier formulación sobre esos valores, deberás tratarlos como decimales...
    Espero haberte aclarado algo.
    Un saludo

    ResponderEliminar
  3. Hola, si quisiera que la macro ejecute directamente el cuadro de diálogo del filtro avanzado con la opción "contiene", qué modificaciones tendría que hacer?
    Muchas gracias

    ResponderEliminar
  4. Hola!
    bueno.. la verdad es que la herramienta 'Filtro avanzado' no ofrece directamente la posibilidad que quieres ('contiene'), como has podido comprobar la ventana diálogo de este filtro avanzado sólo pide seleccionar el rango de datos a filtrar y dónde se encuentran las condiciones de filtro; por tanto si lo que quieres es aplicar un filtro con la opción 'contiene' en esta herramienta deberás incluirlo en el rango de criterios de la forma habitual, esto es, entre asteríscos; por ejemplo, que contenga el texto excel sería:
    '*excel*'.
    Espero haberte aclarado algo.
    saludos

    ResponderEliminar
  5. Hola! Tengo una duda grande... estoy usando FILTRO AVANZADO y me piden que con eso seleccione los 10 mejores ¿cómo hago? ¿hay alguna fórmula? Osea con AUTOFILTRO SE RE PUEDE pero no sé hacerlo con FILTRO AVANZADO :S Agradeceria su ayuda :)

    ResponderEliminar
  6. Hola,
    bueno efectivamente sería mucho más sencillo con el Autofiltro, pero si lo quieres hacer con el Filtro avanzado deberás usar la modalidad de fórmula, es decir, en el rango de criterios deberías añadir una prueba lógica:
    =primera_celda_campo_a_evaluar>=K.ESIMO.MAYOR(rango_campo_evaluar;10)
    El lunes voy a subir un post explicando paso a paso la solución...
    Espero te sirva para adelantar algo.
    Slds

    ResponderEliminar
  7. Muy buenas. El ejemplo esta muy bien, pero te cuento mi duda.... Yo tengo que controlar la uvicación de palet, el problema es que intento aplicar este filtro, me funciona pero no hace lo que quiero.
    La idea es que tengo en una hoja 11 columnas por 27 filas, eso hace un total de 297 celdas. Bien en cualquiera de estas celdas puede uvicarse un pallet con su ref, lo que necesitaria es que cuando haga la busqueda me pueda devolver los pallet independientemente en la columna o fila que sea.
    No se si me he explicado, te dejo mi correo por si acaso. elpuntoblanco@terra.es

    Muchas gracias un saludo.

    ResponderEliminar
  8. Hola,
    habría que ver la estructura de tu tabla, y la disposición de los diferentes elementos de tus 11 columnas (da igual el número de filas).
    Sería conveniente que definieras mejor a qué te refieres con 'devolver después de la búsqueda'.
    Entiendo que se trata de un listado de 11 columnas en las que se encuentra una determinada referencia de pallet en una fila concreta o en varias...???
    Envíame un ejemplo de cómo tienes construida tu tabla y qué necesitas a:
    excelforo@gmail.com
    Un saludo

    ResponderEliminar
  9. Hola, queria sabe si me pueden ayudar, estoy intentando poner un "filtro texto contiene" para una lista de mil nombres y lo sé poner pero me gustaría saber si se puede poner una macro donde pusiera el nombre a buscar en una celda y se ejecutara automaticamente "filtro contien", para saltarme todo el preceso filtro, filtro texto, contiene, seleccionar el cuadrante escribir y buscar. Muchas gracias

    ResponderEliminar
  10. Hola,
    realmente puedes aplicar lo explicado en este mismo post, en la celda en cuestión deberás escribir el texto buscado en el campo concreto entre asteríscos, por ejemplo, si el nombre del comercial buscado contiene el texto 'oj', en la celda concreta del rango de criterio escribiríamos *oj*...
    El resto de la macro funcionaría exactamente igual que en el ejemplo explicado.
    Espero te sirva.
    Slds

    ResponderEliminar
  11. Muchas gracias por tu pronta respuesta, voy a intentarlo aunque estoy un poco pez en esto.
    Muchisimas gracias

    ResponderEliminar
  12. Hola,necesito resolver el siguiente problema: quiero realizar la consulta en una hoja pero los datos se encuentran en otra hoja siempre dentro del mismo archivo. Trato de aplicar lo visto pero no obtengo los resultados deseados.
    Gracias.

    ResponderEliminar
  13. Hola,
    El problema de emplear Filtros avanzados es que sólo funcionan, esto es, sólo puede copiar en una hoja diferente a la que están los datos, cuando Ejecutas el Filtro desde esa hoja.
    Por tanto, tendrías que generar el código de tu macro desde la hoja destino donde quieras copiar el resultado filtrado.
    Slds

    ResponderEliminar
  14. ¡muchas gracias por el instructivo! me es de mucha utilidad. Daniel

    ResponderEliminar
  15. Hola buenas tardes.

    Alguien me podria hechar la mano .. tengo una macro para filtrar determinada informacion de una hoja2 :

    Application.ScreenUpdating = False
    Sheets("Registro").Select
    Rows("4:4").Select
    Selection.AutoFilter
    ActiveSheet.Rang("$A$4:$L$53").AutoFilter Field:=1, Operator:= _
    xlFilterValues, Criteria2:=Array(1, "7/1/2012")


    pero cuando no existe el criterio de busqueda la macro tarda mucho y manda error.. seria posible que la macro mostrara un mensaje " no extiste el criterio de busqueda" y parar la macro cuando no exista esa informacion? que codigo.. muchas gracias ¡¡¡ saludos

    ResponderEliminar
    Respuestas
    1. Hola!!
      bueno, la verdad preferiría ver tu caso concreto, con tu base de datos... ya que veo algo en la sentencia del Autofiltro que me descuadra bastante.
      Para hacer lo que quieres se me ocurre realizar una comprobación inicial del elemento a buscar sobre los distintos elementos que componen el campo a filtrar, y si encuentra uno igual ejecuta el Autofiltro, y en caso que finalice el recorrido de elementos del campo sin coincidencia, entonces que muestre el mensaje de error.
      Quizá se podría probar también con una sentencia de control de errores...
      Pero prefiero tener tu ejemplo, y así probar algo sobre seguro.
      Si no te importa envíamelo a:
      excelforo@gmail.com
      Un saludo

      Eliminar
  16. hola: he aplicado un advancedfilter a una base de datos para determinar un dato especifico, mi pregunta es: como hago para, sobre lo ya filtrado, filtrar nuevamente para extraer un dato nuevo y así sucesivamente. o sea necesito un codigo que me permita filtrar un rango ya filtrado en el mismo lugar. es esto posible?

    ResponderEliminar
    Respuestas
    1. Hola Pablo,
      en principio con el Filtro Avanzado no sería posible, salvo que hayas elegido la opción de Copiar a otro lugar, con lo que tendrías que volver a construir una nueva Región de criterios para aplicar un Nuevo Filtro avanzado sobre el resultado anterior.

      la otra opción es que apliques de una sólo vez todos lo criterios de la primera y segunda 'oleada' de condiciones.

      Espero heberme explicado adecuadamente.
      Un cordial saludo

      Eliminar
    2. hola!!...primero que nada, muchas gracias por contestar tan rápido!...
      esta es la situación mi estimado EF: dispongo de una base de datos de proveedores con detalles de año, mes de partidas, importes, Nº de facturas, Nº de remito, etc... esta es, en una primera instancia, filtrada en un procedimiento que incluye la aplicación de Advancedfilter VBA, y el resultado de lo filtrado es copiado en una hoja resumen)... a esta matriz resultante (en la hoja resumen) deseo aplicarle un nuevo filtrado (por ejemplo por proveedor o mes de partida..) mediante otro procedimiento Adv Filt VBA y que se muestre en el mismo lugar(probé con filterinplace pero nada!)y de este modo poder aplicarle un nuevo filtrado si creo necesario.... complicado lo se!,...sin embargo me interesa la segunda opción que propones, que es lo que se aproxima a lo que deseo... o sea fijar los criterios o condiciones y disparar desde un botón con la función filtros avanzados de la cinta de opciones,... sí! creo que es posible... pero mi duda era si mediante VBA había un código que me permita esa función de filtrar un rango ya filtrado una y otra vez a partir de criterios distintos... voy a probar las dos opciones, ¡¡¡este fin de semana va a ser largo jeje!!!.... te saludo y agradezco por tu generosidad y solidaridad con el que sufre!!! jaja un abrazo..

      Eliminar
    3. Hola Pablo,
      pensé que estabas trabajando sobre la hoja de cálculo y no con VBA.
      En ambos casos parece que la clave, si decides aplicar un Filtro avanzado sobre el resultado copiado de otro filtro avanzado, es indicarle cuál es la región a la que aplicar el filtro y cuáles y donde están los criterios.
      En este caso, habría que definir una variable para el rango a filtrar y otra para el rango de criterios...
      Le daré una pensada, y si se me ocurre algo, intento subir una explicación en el blog.
      Slds

      Eliminar
  17. Hola. mi consulta es la siguiente, cuando me dice guardar el archivo, me sale un mensaje "LAS SIGUIENTES CARACTERISTICAS NO SE PUEDEN GUARDAR EN LIBROS MACRO: PROYECTO DE VB
    ELIJA UN TIPO DE ARCHIVO HABILITADO PARA MACROS EN LA LISTA DE TIPO DE ARCHIVO"

    Hago lo indicado pero cuando guarda, aparece un signo de interrogación en el archivo y habro dixo arxivo y no me funciona la aplicación de macro.

    ayuda pliss.. lo unico para que me me funcionará la aplicación macro fue ir en opciones, centros de confianza, configuracion de macros y habilitar todas las macros (pero no es recomendado código peligroso)

    mi correo es tetra_2424@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola Agustín,
      desde las versión Excel 2007, para grabar libros con macros, se exige guardarlos con la tipología 'Libros de Excel habilitados para macros (*.xlsm)'
      De esta manera al volver el archivo la macro estará disponible, apareciéndote la posibilidad (en función a tu configuración de seguridad de macros) de habilitar dichas macros o no.
      UN cordial saludo

      Eliminar
  18. Cordail saludo.

    Mi consulta es similar a una realizada anteriormente, mas exactamente en junio 22, la diferencia es que yo realizo una consulta a la hoja 2, con un filtro avanzado, el cual he automatizado con una macro, aqui el codigo generado:

    Sub cedula()
    '
    ' cedula Macro
    '
    ' Acceso directo: CTRL+
    '
    Range("Impuestos.xls!datos").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("B5:B6"), CopyToRange:=Range("A10:H33"), Unique:= _
    False
    ActiveWindow.SmallScroll Down:=-18
    End Sub

    Lo que deseo hacer es que cuando se de clic sobre el botón, se ejecute tambien una busqueda de ese criterio y me diga si existe o no, si existe pues que arroje los datos, pero si no que envíe un mensaje diciendo que no se encuentra el dato. La verdad entiendo la lógica del asunto, pero los comandos e instrucciones que debo aplicar y donde los debo aplicar es lo que no comprendo muy bien, pues la programación no es que se me de muy bien.

    Gracias por tu ayuda al respecto.

    ResponderEliminar
    Respuestas
    1. Hola,
      mejor envíame el fichero a
      excelforo@gmail.com

      La idea sería aplicar un condicinal IF.. THEN que determine si existe o no el valor buscado en el rango de criterio (celda B6), si no existe mostrar un msgbox con el error y posterior salida de la macro, con Exit sub; en caso de que si exista, esto es en el ELSE del IF, el código del filtro avanzado.

      Slds

      Eliminar
  19. SOBRE EL VIDEO DE FILTRO AVANZADO CON MACRO, NO ENTIENDO PORQUE VALIDAS LOS CAMPOS DE LA CABECERA DE LA BD EN LA ZONA DE CRITERIOS, ME GUSTARIA SABER LA FINALIDAD DE ELLO. DE ANTEMANO MUCHAS GRACIAS.

    ResponderEliminar
    Respuestas
    1. Hola, buenos días.
      la finalidad es exclusivamente comodidad a la hora de seleccionar los campos sobre los que aplicar los filtros, ten en cuenta que es importante que el área de filtros y la cabecera de la base de datos deben corresponder (ser iguales) para un correcto funcionamiento.
      No tiene más funcionalidad que esa, ya que la macro sencillamente aplica un filtro sobre los campos seleccionados.
      Slds

      P.D.: Por favor, evita escribir en mayúsculas, esto es como si me estuvieras gritando ;-)

      Eliminar
  20. Buen dia ... quisiera hacer una consulta ... como hago para consultar por VBA cual es el valor con el cual se filtro ... me explico .... hay una hoja ya con autofiltro , pero necesito consultar con que dato esta filtrado una columna ... muchas gracias , quedo atento

    ResponderEliminar
    Respuestas
    1. Hola Diego!
      la pregunta es si el filtro aplicado (el que quieres conocer) se realizó también mediante una macro??.
      Si es el caso, bastaría con recuperar el valor de 'Criteria' del método Autofilter. Si has realizado el autofiltro 'manualmente' y a posteriori quieres conocer cuál es el valor filtrado (o valores), podrías aplicar una función personalizada, que detecte para cada Campo qué filtros tiene aplicados.

      Subiré en los próximos días el código con la función... pero te adelanto (por si quieres investigar) que deberás usar la propiedad Autofilter.Filters

      Slds

      Eliminar
    2. Ismael, muchas gracias por tu tiempo y la respuesta. Como nombras el caso es cuando el filtro es manual y luego quiero saber el valor filtrado y me diste una luz utilizando Autofilter y filters, guardando los valores en un array para luego leerlos.
      Saludos !

      Eliminar
    3. Gracias a ti, Diego.
      de todas formas subiré un post con la explicación.
      Un cordial saludo

      Eliminar
  21. Hola!
    tengo un problemilla.. grabé una macro usando filtro avanzado sobre una tabla de datos numéricos con cuatro decimales. El filtro selecciona entre dos valores limites. El problema es que cuando los limites son números decimales la macro se ejecuta pero no arroja los valores ni da error. si efectúo el filtro "a mano" con los mismos criterios si que funciona.

    Tengo seleccionado la coma como separador decimal y cuatro dígitos decimales.

    podrías ayudarme?

    ResponderEliminar
    Respuestas
    1. Hola,
      el problema son precisamente los decimales, ya que en VBA cambia el signo de puntuación para éstos (es un punto); asi que la recomendación que te puedo hacer es que emplees la función Replace para hacer el cambio de este signo, la coma por el punto para los valores con los que trabajes...
      Subiré un post explicando esta sencilla operación que nos vuelve locos a todos.
      Slds

      Eliminar
    2. mil gracias!! que rapidez!! ahora mismo lo cambio!!

      Eliminar
    3. funcionó a la perfección!!
      gracias
      gracias

      Eliminar
  22. Hola
    Tengo una consulta:
    Como haria para que excel me mande una alerta a un correo corporativo mediante un formato condicional
    por ejm tengo este formato
    si c2>30 entonces se coloca rojo pero quiero que tambien me mande una alerta a un correo corporativo como lo haria agradeceria cualquier metodo
    De antemano muchas gracias
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola!
      bueno, para la parte del envío del correo corporativo vas a necesitar una macro.
      Lo primero desde las referencias del editor de VBA activa/instala la 'Microsoft Outlook xx.x Object Library' de mayor número que tengas (la que corresponda a tu versión de office); y luego ya podrás aplicar algún código aplicado a tu caso.
      Te pongo un ejemplo general:
      Sub EmailFromExcel()
      On Error GoTo PROC_EXIT
      Dim OL As New Outlook.Application

      Dim olMail As Outlook.MailItem
      Set olMail = OL.CreateItem(olMailItem)

      Dim SrcSheet As Excel.Worksheet
      Set SrcSheet = Sheets("Sheet1")

      With olMail
      .To = SrcSheet.Range("E3").Text
      .Subject = SrcSheet.Range("E7").Text
      .Body = SrcSheet.Range("E12").Text
      .Display vbModal
      '.Send
      End With

      PROC_EXIT:
      On Error GoTo 0
      OL.Quit
      Set OL = Nothing
      End Sub

      Slds

      Eliminar
  23. TENGO UN PROBLEMA, COMO HAGO PARA QUE EN UNA COLUMNA NO SE ME REPITA EL MISMO CODIGO DE PRODUCTO, QUE ME DIGA QUE ESE CODIGO YA EXISTE

    ResponderEliminar
    Respuestas
    1. Hola, que tal? espero estés bien.
      Yo usaría una Validación de datos sobre el rango.. y configuraría los mensajes de error.
      Un saludo

      P.d.: por favor, evita escribir en mayúsculas, es como si me estuvieras gritando

      Eliminar
  24. Hola
    me funciono el codigo pero yo quiero que me mande el correo automaticamente cuando se cumpla la condicion y con ese ejemplo que me diste me mete al correo para enviarlo
    Un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      condiciona la ejcuación de la macro al valor de la celda
      If celda>30 then EmailFromExcel

      Slds

      Eliminar
  25. hola
    me refiero a que se me envie el correo solo ya que con esta condicion me sigue igual
    Un saludo

    ResponderEliminar
    Respuestas
    1. activa la instrucción
      .Send

      en
      With olMail
      .To = SrcSheet.Range("E3").Text
      .Subject = SrcSheet.Range("E7").Text
      .Body = SrcSheet.Range("E12").Text
      .Display vbModal
      .Send
      End With

      Eliminar
  26. Hola
    que pena molestar tanto pero sigue sin mandarme automaticamente el correo ¿se puede mandar automaticamente cuando cumpla la condicion?

    ResponderEliminar
    Respuestas
    1. Hola,
      no sé donde te puede fallar, lo probé en mi PC y si funcionaba.

      Puedes leer más en las páginas de Microsoft
      http://msdn.microsoft.com/en-us/library/office/ff458119%28v=office.11%29.aspx
      y
      http://support.microsoft.com/kb/816644/es

      Slds

      Eliminar
  27. Hola
    como haria para que cuando el excel me cumpla una condicion VBA me haga automaticamente(sin nesecidad de botones) una operacion por ejemplo
    meter el que cumple la condicion en otra hoja automaticamente
    Un saludo y Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      en el código de la hoja donde esté o se vaya a cumplir esa condición, añade un evento _Change junto a la condición, con un IF... THEN

      Aquí puedes ver un ejemplo similar, asociado a una condición
      http://excelforo.blogspot.com.es/2010/10/vba-cambio-color-de-etiqueta.html

      Espero te sirva
      Slds

      Eliminar
  28. hola como hago si debo filtrar una tabla por el mes y las fechas estan en la columna C en formatos dd/mm/aaa y necesito que pueda filtrar por el mes actual y luego pueda filtrar por el mes anterior o que el usuario puede elegir que le muestre determinado mes.

    ResponderEliminar
    Respuestas
    1. Hola Ibeth,
      para realizar ese filtro lo mejor es emplear el Autofiltro, esta herramienta detecta el tipo de datos del campo (columna C) y muestra diferentes criterios de Filtros de fecha.
      Slds cordiales

      Eliminar
  29. Hola
    Lo primero gracias por compartir.
    He seguido tu video y funciona, pero lo he hecho con 20 registros y 4 campos (Nombre, Direccion, Altura, Altura)el campo altura para altura maxima y altura minima, y se demora como 5 segundos en crear el resultado. Es normal tanto tiempo?¿Se puede optimizar de alguna manera?
    De antemano Gracias y un saludo
    Ppri

    ResponderEliminar
    Respuestas
    1. Hola Francis,
      muchas gracias a ti.

      Respecto a tu cuestión, no, no es normal que en filtrar tan pocos registros tarde 5 segundos...
      Prueba a realizar el filtro normalmente (sin macros) a ve cuanto tarda... si lo hace de manera instantanea algo 'raro' hay en el asunto.
      Envíame si quieres el fichero a
      excelforo@gmail.com
      y le echo un vistazo
      Slds cordiales

      Eliminar
    2. Hola
      Encontré el problema y la solución.
      El problema está en el botón borrar. Si la celda, en este caso B30, está vacía, y pulsamos dicho botón, Excel 2007 crea la hoja desde b30:??1048576 y si grabamos el fichero nos crea uno con (en mi caso) 29.847 KB !!!!! por lo tanto la demora se debe a que mira el filtro en todo ese rango.
      Si esto pasa:Marcar todas las filas desde b30 hasta la 1048576 (Seleccionando todas las filas desde los números de fila) y eliminarlas.
      Tarda un poco y a veces se cuelga excel pero se recupera.
      También se puede añadir un If para comprobar que b30 tiene datos, o quizás haya otra solución.
      Se nota este fallo enseguida ya que cualquier cosa que se haga a partir de ese momento se ralentiza muchísimo.
      Un saludo

      Eliminar
    3. Hola Francis,
      veo que estás hablando de tu caso en particular... ya que no corresponden con el código subido en el post (que se mueve con el 'método final').. realmente no sé a qué te refieres con ese rango B30:??1048576... supongo quieres indicar al rango por debajo del resultado filtrado devuelto; pero si la macro de Borrado únicamente trabaja sobre celdas con datos, por qué habría que borrar ese B30:??1048576 ????

      En todo caso, he replicado el ejemplo y ejecutado las macros, y a mí no me tarda esos tiempos de los que tu hablas, es más es inmediato...

      Un cordial saludo

      Eliminar
    4. Hola Ismael
      Te pego el código de las macros que utilizo y también te envio mi libro por correo.

      Sub Filtro_Avanzado()
      '
      ' Filtro_Avanzado Macro
      '

      '
      Range("Tabla2[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
      Range("Criterios1"), CopyToRange:=Range("B30"), Unique:=False
      End Sub
      Sub Borrar()
      '
      ' Borrar Macro
      '

      '
      Range("B30").Select
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Delete Shift:=xlToLeft
      End Sub

      Como ves mi b30 corresponde a tu a17 es la única diferencia.
      Lo normal es que nadie pulse el boton de borrar si no se ha pulsado antes el de filtro, pero.... yo lo hice sin querer y surgio este problema.
      Para reproducir el problema, te posicionas en cualquier celda vacia y pulsas el boton de borrar se te selecciona el resto de la hoja y si grabas el libro, a mi me produce un fichero de 29 Mg.
      Si no te pasa igual es un problema de mi configuración a hay brujas por ahí...
      Gracias por tu tiempo
      Un saludo

      Eliminar
    5. Vale!!
      lógico en ese caso el método final 8sin nada que seleccionar) te marcará hasta la última columna y la última fila, es decir, unas 'pocas' celdas a borrar.
      La solución entonces pasaría por añadir como indicabas un IF, por ejemplo:
      Sub Borrar()
      Range("B30").Select
      If Selection = "" Then
      MsgBox "No hay celdas que borrar"
      Exit Sub
      Else
      Range(Selection, Selection.End(xlToRight)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Delete Shift:=xlToLeft
      End If
      End Sub

      De todas formas he probado ha ejecutarlo sin nada seleccionado y en mi equipo (que te aseguro no es ninguna maravilla) ha tardado casi dos segundos... eso sí, el fichero se guarda con el tamaño que indicas (pero no creo eso se demasiado influyente).
      Slds

      Eliminar
    6. Hola otra vez Ismael
      La solución que me diste es casi buena :-((
      Sigue "apoderandose" de todas las celdas que borra y sigue generando un fichero enorme que es lento de trabajar.
      De todas formas he intentado hacer alguna mejora que consiste en crear un Userform con 4 Combobox rellenados con los campos correspondientes y 2 Textbox para la altura colocando los datos en las celdas de criterios, y ha dejado de funcionar el filtro :-((
      La sintaxis de los combos es
      ComboBox1.RowSource = "Tabla2[N_COMUN]"
      ComboBox2.RowSource = "Tabla2[FAMILIA]"
      ComboBox3.RowSource = "Tabla2[HOJAS]"
      ComboBox4.RowSource = "Tabla2[CALLE]"
      al inicializar el Formulario.
      Las celdas de criterios recoge los datos asi:
      Private Sub CommandButton1_Click()
      Sheets("BBDD").Select
      Borrar
      Range("q3").Select
      ActiveCell.Value = ComboBox1
      ActiveCell.Offset(0, 1).Value = ComboBox2
      ActiveCell.Offset(0, 2).Value = ComboBox3
      ActiveCell.Offset(0, 3).Value = ComboBox4
      ActiveCell.Offset(0, 4).Value = ">" & TextBox1
      ActiveCell.Offset(0, 5).Value = "<" & TextBox2
      Filtro_Avanzado

      End Sub

      Me puedes ayudar para ver donde está mi fallo?

      Gracias de nuevo

      Eliminar
    7. Hola, si los filtros 4 y 5 (con mayor y menor) tienen valores con decimales, deberás leer con atención esta entrada
      http://excelforo.blogspot.com.es/2013/05/vba-los-decimales-y-los-filtros.html
      ya que el tratamiento de decimales con los filtros es algo delicado (pero fácil).

      Del tema del tamaño del fichero no hemos tomado ninguna acción al respecto... probablemente se haya quedado con algo en memoria que lo mantiene de ese tamaño.

      Slds

      Eliminar
    8. Hola
      Fantástico!!
      La función Replace funciona perfectamente.
      En cuanto al tamaño del fichero, he añadido al evento salir del libro unas líneas para que elimine las filas desde la última con datos de la BBDD hasta el final y pasa de 29 MG a 110 Kb.
      Gracias por todo
      Un saludo.

      Eliminar
  30. Hola! excelente blog!
    Te consulto lo siguiente, tengo esta macro (filtro avanzado):

    Macro1 Macro
    '
    ' Acceso directo: CTRL+a
    '
    Range("BASE").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
    "CRITERIOS1"), CopyToRange:=Range("B25:T25"), Unique:=False

    El problema es que al colocar un criterio con sgnos > y < en fechas, no me trae nada. Si hago ese mismo filtro (mismos criterios) manualmente sale bien.

    Te agrederia me ayudes

    ResponderEliminar
    Respuestas
    1. Hola Leandro,
      es el eterno problema de las fechas en Excel, y las diferencias en los sistemas dd/mm/aaaa y mm/dd/yyyy.
      Mi recomendación sería que en las celdas de la hoja donde coloques los criterios, no pongas directamente la fecha:
      <31/03/2013
      si no que pongas la fórmula
      ="<"&FECHA(2013;3;31)
      así harás entender claramente a Excel cuál es la fecha con la que trabajar.

      Esto es extensible a cualquier otra faceta de Excel y las fechas, cuando tengas problemas emplea la función FECHA.

      Slds cordiales

      Eliminar
  31. Muchas gracias por tu respuesta, lo estoy usando asi.
    Te hago otra consulta: en la parte de criterios (del filtro avanzado) yo deje 4 lineas.
    El inconveniente es que, para automatizarlo, la macro pide un rango. Y en caso de no completar las cuatro lineas el filtro avanzado me traera el reporte completo. Esto lo solucione haciendo una macro por linea (es decir, las cuatro iguales solo que cambia el rango de criterio), y aplique un condicional (formula SI, y formato condicional) para que solo sea visible el hipervinculo a la macro que corresponde en funcion de la cantidad de lineas completadas.
    Concretamente la pregunta es, este es el mejor modo de hacerlo? no se puede hacer todo en una misma macro? mas que nada porque de este modo tengo cuatro botones de macro que ademas van cambiando de lugar, y no me parece que quede tan bien.

    gracias!!

    ResponderEliminar
    Respuestas
    1. Hola Leandro,
      para concretar el rango de criterios a las 4 líneas (o las que sean), podrías definir una variable en la macro, de tal forma que teniendo claro el comienzo del rango (la cabecera del área de restricciones), puedas encontrar con el modo Fin la última celda de dicho área.
      Por ejemplo, suponiendo el área de restricciones comience en A1 el rango variable podría quedar así:
      Set Rng = Range(Range("A1"), Range("A1").End(xlDown))
      Rng.Select 'o usar Rng en la instrucción del filtro avanzado.

      Slds

      Eliminar
  32. Hola tengo un filtro avanzado que en general funciona bien, pero recientemente me pidieron que incorporara hipervinculos, bueno el filtro funciona pero el tipo de pegado solo contempla texto o un valor numérico, y pierdo el vinculo con el archivo al pegar solo el nombre de este.
    si me pueden ayudar se los agradecería, e intentado modificar el código pero no e obtenido buenos resultados.

    ResponderEliminar
    Respuestas
    1. Hola,
      alguna vez he resulto una situación parecida olvidándome de la herramienta Filtro avanzado, e implementando un filtro en VBA 'manual'.. en el que se recorren los diferentes registros, y sólo cuando cumplan las condiciones dadas copie y pegue toda la fila del registro.
      Con este copiado y pegado se arrastra todas las características originales, incluido el hipervínculo.

      Espero haberte orientado.
      Slds cordiales

      Eliminar
  33. Hola, mi caso es que tengo una BD en excel con de personal contiene nombres, cargos y mas de 20 entrenamientos que no todos realizan pero en donde consignamos las fechas en que las realizan. Necesito saber cuando los entrenamientos esten a punto de vencerse (un año), pero de cada entrenamiento por separado, lo he intentado dejando en hojas separadas los entrenamiento, pero me resulta dificil de actualizar, cuando el personal sale o cambia de cargo toca revisar todas las hojas. Espero puedan ayudarme con ideas de como aplico filtros y macros para facilitar estos datos.

    ResponderEliminar
    Respuestas
    1. Hola Ana,
      lo siento de verás peor no me hago una composición de qué necesitas ni cómo tienes distribuidos los dato...
      si lo prefieres envíame un correo a
      excelforo@gmail.com
      con una explicación y ejemplos de lo que necesitas.

      Un saludo

      Eliminar
  34. Hola,
    Estoy haciendo una macro con un filtro avanzado. Resulta que el rango de criterios es variable, es decir, va desde la celda F2 hasta la F100, ciento y pico,... dependiendo de cual sea la última celda de la columna F con datos. Así que mi duda es saber como programar el filtro para que mi rango de criterios pueda variar entre F2 y la ultima celda ocupada de F.
    Un saludo!! Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      aquí tienes un ejemplo de cómo obtener siempre la última fila con datos... la que determina el fin de tu rango:
      http://excelforo.blogspot.com.es/2011/09/vba-como-insertar-filas-el-metod-insert.html

      En concreto la parte donde dice...
      'Encontramos la última fila con valores CADA VEZ
      Set hoja = Sheets("Hoja1")
      UltFila = hoja.Range("A" & hoja.Rows.Count).End(xlUp).Row
      rng = hoja.Range("A2:A" & UltFila).Address

      Saludos

      Eliminar
    2. Hola,
      Gracias por contestar tan rápido. Increíble! Lo de encontranr la última fila con datos ya sabía hacerlo más o menos. El problema es que no se como usarlo luego para hacer el rango de criterios en el filtro avanzado. ¿Sería algo asi? No se que poner en Criteria Range

      Sheets("CRM001").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=rng, CopyToRange:=Range("G1:J1"), Unique:= _
      True

      Y otra cosita, programé la macro en Excel 2003, pero al tratar de abrirlo en el 2010, el programa no da fallo pero el siguiente filtro no me funciona:

      Sheets("CRM001").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Range("C2:D3"), CopyToRange:=Range("E1:H1"), Unique:=True

      No se qué pasa en la versión de 2010, porque grabo esta macro del filtro avanzado y funciona al grabarla, pero luego al ejecutarla no me filtra. Solo me va cuando la hago manualmente. ¿Qué estoy haciendo mal?

      Gracias de antemano. Un saludo

      Eliminar
    3. Hola,
      siempre es delicado trabajar con una macro de un Filtro avanzado.
      Te diría, si ya tienes definido el 'rng' para criterios, que probaras con:

      Sheets("CRM001").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=rng, CopyToRange:=Range("G1"), Unique:= True


      Salvo que ya tengas puestos los nombres de los cuatro campos en E1:H1.

      Es decir, el fallo puede estar en que lo que quieres obtener de A:V con los criterios expuestos en 'rng', no lo tienes detallado en E1:H1.
      También es posible que si tratas de filtrar (criterios) sobre valores con decimales, la macro no lo detecte bien:
      http://excelforo.blogspot.com.es/2013/05/vba-los-decimales-y-los-filtros.html

      En resumen, verifica el tema de los decimales, comprueba que tienes en E1:H1(si corresponde) los nombres de los campos que deseas obtener de A:V (si no es el caso pon sólo E1).

      Saludos

      Eliminar
    4. Muchas gracias por tu ayuda. He solucionado el tema de que no me saliera uno de los filtros; efectivamente habia un problema con los datos a filtrar y los decimales.
      Pero el otro problema sigo sin resolverlo. Me refiero a lo de usar el rango variable como criteriode filtro. Esto es lo que estoy poniendo:

      Public UltFila As Long
      Public rng As Variant
      Dim hoja As Worksheet

      Set hoja = Sheets("Reporte")
      UltFila = Range("F" & hoja.Rows.Count).End(xlUp).Row
      rng = Range("F2:F" & UltFila).Address

      Sheets("CRM001").Columns("A:W").AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=rng, CopyToRange:=Range("G1:J1"), Unique:= _
      True

      Como datos adicionales te comento que la hoja "CRM001" es donde están los datos y la hoja "Reporte" es a donde quiero llevar los datos filtrados. En la hoja "Reporte" de G1 a J1 tengo puesto los títulos de los campos que quiero filtrar. Y los criterios van de F2 hasta F? en la hoja "Reporte"

      Un saludo

      Eliminar
    5. Hola,
      mejor mándame el fichero a
      excelforo@gmail.com
      para que lo pueda revisar.
      Slds

      Eliminar
  35. Muchas gracias por tu ayuda, pero no puedo enviártelo. El archivo contiene datos confidenciales. Seguiré dándole vueltas a ver si lo saco
    Un saludo

    ResponderEliminar
    Respuestas
    1. Al final lo he conseguido poniendo esto:

      UltFila = Sheets("Reporte").Range("F" & Sheets("Reporte").Rows.Count).End(xlUp).Row

      Set rng = Range("F2:F" & UltFila)
      Sheets("CRM001").Columns("A:W").AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=rng, CopyToRange:=Range("G1:J1"), Unique:= _
      True

      Muchísimas gracias!!! Sin tu ayuda no hubiera sido capaz.
      Un saludo

      Eliminar
  36. Hola, cuando los criterios son un rango de fechas (>03/07/2014 y <06/07/2014) el botón que ejecuta la macro Filtro_Avanzado no me devuelve registros cuando sí los hay. ¿es posible que sea porque no entiende los signos de >,<?

    ResponderEliminar
    Respuestas
    1. Hola de nuevo: He tenido que introducir los criterios de la siguiente forma, =">"&F1, y en la celda F1 he introducido el valor de la fecha.
      Muchas gracias por explicar como automatizar el filtro avanzado a través de una macro.

      Eliminar
    2. Bueno,
      sin duda trabajar con fechas es siempre delicado...
      debería haberte funcionado sin problemas (como lo intentaste >03/07/2014), pero cuando falla debemos tener recursos..
      Uno es como indicas, referenciándolo a otra celda, otra podría haber sido empleando la función fecha:
      >FECHA(2014;7;3)

      Un cordial saludo

      Eliminar
  37. hola tengo un problema grabe una macro generando un filtro avanzado pero no al momento de ejecutar la macro me sale los mismos criterios , ya que mi necesidad de mi rango es que puede ser 1 o varios criterios de una base
    que puedo hacer??

    ResponderEliminar
    Respuestas
    1. Hola Cristian,
      es habitual que los criterios, al grabar la macro con el asistente, de un filtro avanzado no funcionen correctamente..
      Por ejemplo, un fallo muy habitual es el de lo decimales:
      http://excelforo.blogspot.com.es/2013/05/vba-los-decimales-y-los-filtros.html

      También es frecuente, si has empleado la opción de 'copiar a:', que no se haya reflejado correctamente...

      Habría que ver el contexto de los criterios...

      Un saludo

      Eliminar

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