También hay muchas funcionalidades de Excel disponibles para su uso en VBA, y el Autofiltro El método es una de esas funciones.
Si tiene un conjunto de datos y desea filtrarlo usando un criterio, puede hacerlo fácilmente usando la opción Filtro en la cinta de Datos.
Y si desea una versión más avanzada, también hay un filtro avanzado en Excel.
Entonces, ¿por qué utilizar el autofiltro en VBA?
Si solo necesita filtrar datos y hacer algunas cosas básicas, le recomendaría que se ciña a la funcionalidad de filtro incorporada que ofrece la interfaz de Excel.
Debe usar VBA Autofilter cuando desee filtrar los datos como parte de su automatización (o si le ayuda a ahorrar tiempo al hacer que sea más rápido filtrar los datos).
Por ejemplo, suponga que desea filtrar rápidamente los datos en función de una selección desplegable y luego copiar estos datos filtrados en una nueva hoja de trabajo.
Si bien esto se puede hacer usando la funcionalidad de filtro incorporada junto con algo de copiar y pegar, puede llevarle mucho tiempo hacerlo manualmente.
En tal escenario, el uso de VBA Autofilter puede acelerar las cosas y ahorrar tiempo.
Nota: Cubriré este ejemplo (sobre el filtrado de datos en función de una selección desplegable y la copia en una nueva hoja) más adelante en este tutorial.
Sintaxis del filtro automático de Excel VBA
Expresión. Autofiltro (_Campo_, _Criterios1_, _Operador_, _Criterios2_, _VisibleDropDown_)
- Expresión: Este es el rango en el que desea aplicar el filtro automático.
- Campo: [Argumento opcional] Este es el número de columna que desea filtrar. Esto se cuenta desde la izquierda en el conjunto de datos. Entonces, si desea filtrar datos según la segunda columna, este valor sería 2.
- Criterio1: [Argumento opcional] Este es el criterio según el cual desea filtrar el conjunto de datos.
- Operador: [Argumento opcional] En caso de que también esté utilizando el criterio 2, puede combinar estos dos criterios según el operador. Los siguientes operadores están disponibles para su uso: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Criterios2: [Argumento opcional] Este es el segundo criterio en el que puede filtrar el conjunto de datos.
- VisibleDropDown: [Argumento opcional] Puede especificar si desea que el icono desplegable del filtro aparezca en las columnas filtradas o no. Este argumento puede ser VERDADERO o FALSO.
Aparte de Expression, todos los demás argumentos son opcionales.
En caso de que no use ningún argumento, simplemente aplicará o eliminará los íconos de filtro a las columnas.
Sub FilterRows () Hojas de trabajo ("Filtrar datos"). Rango ("A1"). AutoFilter End Sub
El código anterior simplemente aplicaría el método Autofilter a las columnas (o si ya está aplicado, lo eliminará).
Esto simplemente significa que si no puede ver los íconos de filtro en los encabezados de las columnas, comenzará a verlos cuando se ejecute el código anterior, y si puede verlo, se eliminará.
En caso de que tenga datos filtrados, eliminará los filtros y le mostrará el conjunto de datos completo.
Ahora veamos algunos ejemplos del uso de Excel VBA Autofilter que aclararán su uso.
Ejemplo: filtrado de datos según una condición de texto
Suponga que tiene un conjunto de datos como se muestra a continuación y desea filtrarlo según la columna "Elemento".
El siguiente código filtraría todas las filas donde el elemento es "Impresora".
Sub FilterRows () Hojas de trabajo ("Hoja1"). Rango ("A1"). Campo de autofiltro: = 2, Criterio1: = "Impresora" End Sub
El código anterior se refiere a Sheet1 y dentro de él, se refiere a A1 (que es una celda en el conjunto de datos).
Tenga en cuenta que aquí hemos utilizado Field: = 2, ya que la columna del elemento es la segunda columna de nuestro conjunto de datos desde la izquierda.
Ahora, si estás pensando, ¿por qué necesito hacer esto usando un código VBA? Esto se puede hacer fácilmente usando la funcionalidad de filtro incorporada.
¡Tienes razón!
Si esto es todo lo que quiere hacer, utilice mejor la funcionalidad de filtro incorporada.
Pero a medida que lea el tutorial restante, verá que esto se puede combinar con un código adicional para crear una automatización potente.
Pero antes de mostrarte esos, permíteme primero cubrir algunos ejemplos para mostrarte lo que puede hacer todo el método AutoFilter.
haga clic aquí para descargar el archivo de ejemplo y seguirlo.
Ejemplo: varios criterios (Y / O) en la misma columna
Supongamos que tengo el mismo conjunto de datos, y esta vez quiero filtrar todos los registros donde el elemento es "Impresora" o "Proyector".
El siguiente código haría esto:
Sub FilterRowsOR () Hojas de trabajo ("Hoja1"). Rango ("A1"). Campo de autofiltro: = 2, Criterio1: = "Impresora", Operador: = xlOr, Criterio2: = "Proyector" Fin Sub
Tenga en cuenta que aquí he utilizado el xlOR operador.
Esto le dice a VBA que use ambos criterios y filtre los datos si se cumple alguno de los dos criterios.
Del mismo modo, también puede utilizar los criterios AND.
Por ejemplo, si desea filtrar todos los registros donde la cantidad es más de 10 pero menos de 20, puede usar el siguiente código:
Sub FilterRowsAND () Hojas de trabajo ("Sheet1"). Range ("A1"). Autofilter Field: = 4, Criteria1: = "> 10", _ Operador: = xlAnd, Criteria2: = "<20" End Sub
Ejemplo: varios criterios con diferentes columnas
Suponga que tiene el siguiente conjunto de datos.
Con Autofilter, puede filtrar varias columnas al mismo tiempo.
Por ejemplo, si desea filtrar todos los registros donde el artículo es "Impresora" y el Representante de ventas es "Marca", puede usar el siguiente código:
Sub FilterRows () With Worksheets ("Sheet1"). Range ("A1") .AutoFilter field: = 2, Criteria1: = "Printer" .AutoFilter field: = 3, Criteria1: = "Mark" End With End Sub
Ejemplo: filtrar los 10 registros principales mediante el método de autofiltro
Suponga que tiene el siguiente conjunto de datos.
A continuación se muestra el código que le dará los 10 registros principales (según la columna de cantidad):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Campo de autofiltro: = 4, Criteria1: = "10", Operador: = xlTop10Items End Sub
En el código anterior, he usado ActiveSheet. Puede utilizar el nombre de la hoja si lo desea.
Tenga en cuenta que en este ejemplo, si desea obtener los 5 elementos principales, simplemente cambie el número en Criterio1: = ”10 ″ de 10 a 5.
Entonces, para los 5 elementos principales, el código sería:
Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Campo de autofiltro: = 4, Criteria1: = "5", Operador: = xlTop10Items End Sub
Puede parecer extraño, pero no importa cuántos elementos principales desee, el valor del operador siempre permanece xlTop10Items.
Del mismo modo, el siguiente código le daría los 10 elementos inferiores:
Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Campo de autofiltro: = 4, Criteria1: = "10", Operador: = xlBottom10Items End Sub
Y si desea los 5 elementos inferiores, cambie el número en Criterio1: = ”10 ″ de 10 a 5.
Ejemplo: filtrar el 10 por ciento superior mediante el método de autofiltro
Suponga que tiene el mismo conjunto de datos (como se usó en los ejemplos anteriores).
A continuación se muestra el código que le dará los registros del 10 por ciento superior (según la columna de cantidad):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Campo de autofiltro: = 4, Criteria1: = "10", Operador: = xlTop10Percent End Sub
En nuestro conjunto de datos, dado que tenemos 20 registros, devolverá los 2 registros principales (que es el 10% del total de registros).
Ejemplo: uso de caracteres comodín en Autofilter
Suponga que tiene un conjunto de datos como se muestra a continuación:
Si desea filtrar todas las filas en las que el nombre del elemento contiene la palabra "Tablero", puede utilizar el siguiente código:
Sub FilterRowsWildcard () Worksheets ("Sheet1"). Range ("A1"). Autofilter Field: = 2, Criteria1: = "* Board *" End Sub
En el código anterior, he utilizado el carácter comodín * (asterisco) antes y después de la palabra "Tablero" (que es el criterio).
Un asterisco puede representar cualquier número de caracteres. Por lo tanto, esto filtraría cualquier elemento que tenga la palabra "tablero".
Ejemplo: copiar filas filtradas en una hoja nueva
Si desea no solo filtrar los registros según los criterios, sino también copiar las filas filtradas, puede usar la siguiente macro.
Copia las filas filtradas, agrega una nueva hoja de trabajo y luego pega estas filas copiadas en la nueva hoja.
Sub CopyFilteredRows () Dim rng As Range Dim ws As Worksheet If Worksheets ("Sheet1"). AutoFilterMode = False Then MsgBox "No hay filas filtradas" Salir Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Worksheets.Add rng.Copy Range ("A1") End Sub
El código anterior verificaría si hay filas filtradas en Sheet1 o no.
Si no hay filas filtradas, mostrará un cuadro de mensaje indicándolo.
Y si hay filas filtradas, las copiará, insertará una nueva hoja de trabajo y pegará estas filas en esa hoja de trabajo recién insertada.
Ejemplo: filtrar datos en función de un valor de celda
Usando Autofilter en VBA junto con una lista desplegable, puede crear una funcionalidad en la que tan pronto como seleccione un elemento del menú desplegable, se filtran todos los registros de ese elemento.
Algo como se muestra a continuación:
haga clic aquí para descargar el archivo de ejemplo y seguirlo.
Este tipo de construcción puede ser útil cuando desea filtrar datos rápidamente y luego usarlos más en su trabajo.
A continuación se muestra el código que hará esto:
Private Sub Worksheet_Change (ByVal Target As Range) If Target.Address = "$ B $ 2" Then If Range ("B2") = "All" Then Range ("A5"). Autofiltro Else Range ("A5"). Campo de autofiltro : = 2, Criteria1: = Rango ("B2") End If End If End Sub
Este es un código de evento de hoja de trabajo, que se ejecuta solo cuando hay un cambio en la hoja de trabajo y la celda de destino es B2 (donde tenemos el menú desplegable).
Además, se utiliza una condición If Then Else para comprobar si el usuario ha seleccionado "Todos" en el menú desplegable. Si se selecciona Todo, se muestra el conjunto de datos completo.
Este código NO se coloca en un módulo.
En cambio, debe colocarse en el backend de la hoja de trabajo que tiene estos datos.
Estos son los pasos para colocar este código en la ventana de código de la hoja de trabajo:
- Abra el Editor de VB (método abreviado de teclado - ALT + F11).
- En el panel Explorador de proyectos, haga doble clic en el nombre de la hoja de trabajo en el que desea esta función de filtrado.
- En la ventana de código de la hoja de trabajo, copie y pegue el código anterior.
- Cierre el editor de VB.
Ahora, cuando use la lista desplegable, automáticamente filtrará los datos.
Este es un código de evento de hoja de trabajo, que se ejecuta solo cuando hay un cambio en la hoja de trabajo y la celda de destino es B2 (donde tenemos el menú desplegable).
Además, se utiliza una condición If Then Else para comprobar si el usuario ha seleccionado "Todos" en el menú desplegable. Si se selecciona Todo, se muestra el conjunto de datos completo.
Activar / desactivar el autofiltro de Excel usando VBA
Al aplicar Autofiltro a un rango de celdas, es posible que ya haya algunos filtros en su lugar.
Puede utilizar el siguiente código para desactivar los filtros automáticos aplicados previamente:
Sub TurnOFFAutoFilter () Hojas de trabajo ("Sheet1"). AutoFilterMode = False End Sub
Este código verifica todas las hojas y elimina cualquier filtro que se haya aplicado.
Si no desea desactivar los filtros de toda la hoja, sino solo de un conjunto de datos específico, use el siguiente código:
Sub TurnOFFAutoFilter () If Worksheets ("Sheet1"). Range ("A1"). AutoFilter Then Worksheets ("Sheet1"). Range ("A1"). Autofilter End If End Sub
El código anterior verifica si ya hay filtros en su lugar o no.
Si los filtros ya están aplicados, los elimina, de lo contrario, no hace nada.
Del mismo modo, si desea activar Autofiltro, use el siguiente código:
Sub TurnOnAutoFilter () Si no son hojas de trabajo ("Hoja1"). Rango ("A4"). Autofiltro Luego, hojas de trabajo ("Hoja1"). Rango ("A4"). Autofiltro Fin si Fin Sub
Compruebe si el autofiltro ya está aplicado
Si tiene una hoja con varios conjuntos de datos y desea asegurarse de saber que no hay filtros ya implementados, puede usar el siguiente código.
Sub CheckforFilters () Si ActiveSheet.AutoFilterMode = True Entonces MsgBox "Ya hay filtros en su lugar" Else MsgBox "No hay filtros" End If End Sub
Este código utiliza una función de cuadro de mensaje que muestra un mensaje "Ya hay filtros en su lugar" cuando encuentra filtros en la hoja; de lo contrario, muestra "No hay filtros".
Mostrar todos los datos
Si tiene filtros aplicados al conjunto de datos y desea mostrar todos los datos, use el siguiente código:
Sub ShowAllData () Si ActiveSheet.FilterMode Entonces ActiveSheet.ShowAllData End Sub
El código anterior verifica si FilterMode es VERDADERO o FALSO.
Si es cierto, significa que se aplicó un filtro y usa el método ShowAllData para mostrar todos los datos.
Tenga en cuenta que esto no elimina los filtros. Los iconos de filtro todavía están disponibles para su uso.
Uso de Autofiltro en hojas protegidas
De forma predeterminada, cuando protege una hoja, los filtros no funcionan.
En caso de que ya tenga filtros en su lugar, puede habilitar Autofiltro para asegurarse de que funcione incluso en hojas protegidas.
Para hacer esto, marque la opción Usar Autofiltro mientras protege la hoja.
Si bien esto funciona cuando ya tiene filtros en su lugar, en caso de que intente agregar Autofilters usando un código VBA, no funcionará.
Dado que la hoja está protegida, no permitiría que se ejecutara ninguna macro y realizar cambios en el autofiltro.
Por lo tanto, debe usar un código para proteger la hoja de trabajo y asegurarse de que los filtros automáticos estén habilitados en ella.
Esto puede ser útil cuando ha creado un filtro dinámico (algo que cubrí en el ejemplo - "Filtrar datos basados en un valor de celda").
A continuación se muestra el código que protegerá la hoja, pero al mismo tiempo, le permitirá usar Filtros y macros VBA en ella.
Private Sub Workbook_Open () With Worksheets ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Contenidos: = True, UserInterfaceOnly: = True End With End Sub
Este código debe colocarse en la ventana de código de ThisWorkbook.
Estos son los pasos para poner el código en la ventana de código de ThisWorkbook:
- Abra el Editor de VB (método abreviado de teclado: ALT + F11).
- En el panel Explorador de proyectos, haga doble clic en el objeto ThisWorkbook.
- En la ventana de código que se abre, copie y pegue el código anterior.
Tan pronto como abra el libro de trabajo y habilite las macros, ejecutará la macro automáticamente y protegerá Sheet1.
Sin embargo, antes de hacer eso, especificará "EnableAutoFilter = True", lo que significa que los filtros también funcionarían en la hoja protegida.
Además, establece el argumento "UserInterfaceOnly" en "Verdadero". Esto significa que mientras la hoja de trabajo está protegida, el código de macros de VBA continuará funcionando.
También te pueden gustar los siguientes tutoriales de VBA:
- Bucles de Excel VBA.
- Filtrar celdas con formato de fuente en negrita.
- Grabación de una macro.
- Ordenar datos usando VBA.
- Ordene las pestañas de la hoja de trabajo en Excel.
