Excel VBA Autofiltro: Una Guía Completa con Ejemplos

Una gran cantidad de funcionalidades de Excel también están disponibles para ser utilizadas en VBA – y el método de Autofiltro es una de esas funcionalidades.

Si usted tiene un conjunto de datos y desea filtrarlo utilizando un criterio, puede hacerlo fácilmente utilizando la opción de Filtro en la cinta de Datos.

Y si desea una versión más avanzada de la misma, hay un filtro avanzado en Excel también.

¿Entonces por qué usar el Autofiltro en VBA?

Si sólo necesita filtrar datos y hacer algunas cosas básicas, recomendaría atenerse a la funcionalidad de Filtro incorporado que ofrece la interfaz de Excel.

Debería usar el Autofiltro de VBA cuando quiera filtrar los datos como parte de su automatización (o si le ayuda a ahorrar tiempo haciendo más rápido el filtrado de los datos).

Por ejemplo, suponga que quiere filtrar rápidamente los datos basándose en una selección desplegable y, a continuación, copiar estos datos filtrados en una nueva hoja de trabajo.

Aunque esto puede hacerse utilizando 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 Autofiltro puede acelerar las cosas y ahorrar tiempo.

Nota: Voy a cubrir este ejemplo (en el filtrado de datos basado en una selección desplegable y copiar en una nueva hoja) más adelante en este tutorial.

Sintaxis del Autofiltro VBA de Excel

Expression. AutoFilter( _Field_ , _Criteria1_ , _Operator_ , _Criteria2_ , _VisibleDropDown_ )

• Expresión: Es el rango sobre el que se quiere aplicar el autofiltro.
• Campo: Es el número de columna que se quiere filtrar. Se cuenta desde la izquierda en el conjunto de datos. Por lo tanto, si desea filtrar los datos basados en la segunda columna, este valor sería 2.
• Criterio1: Este es el criterio en base al cual desea filtrar el conjunto de datos.
• Operador: En el caso de que estés utilizando también el criterio 2, puedes combinar estos dos criterios basándote en el Operador. Los siguientes operadores están disponibles para su uso: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
• Criteria2: Este es el segundo criterio en el que puede filtrar el conjunto de datos.
• VisibleDropDown: Puede especificar si desea que el icono desplegable del filtro aparezca en las columnas filtradas o no. Este argumento puede ser TRUE o FALSE.

Aparte de Expression, todos los demás argumentos son opcionales.

En caso de no utilizar ningún argumento, simplemente aplicaría o eliminaría los iconos de filtro a las columnas.

Sub FilterRows()Worksheets("Filter Data").Range("A1").AutoFilterEnd Sub

El código anterior simplemente aplicaría el método Autofiltro a las columnas (o si ya está aplicado, lo eliminaría).

Esto significa simplemente que si no puedes ver los iconos de filtro en los encabezados de las columnas, empezarás a verlo cuando se ejecute este código de arriba, y si puedes verlo, entonces se eliminará.

En caso de que tengas algún dato filtrado, eliminará los filtros y te mostrará el conjunto de datos completo.

Ahora veamos algunos ejemplos de uso del Autofiltro de Excel VBA que dejarán claro su uso.

Ejemplo: Filtrado de datos basado en una condición de texto

Suponga que tiene un conjunto de datos como el que se muestra a continuación y desea filtrarlo basándose en la columna ‘Elemento’.

El siguiente código filtraría todas las filas en las que el elemento es ‘Impresora’.

Sub FilterRows()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"End Sub

El código anterior se refiere a la Hoja1 y dentro de ella, se refiere a A1 (que es una celda en el conjunto de datos).

Nótese que aquí hemos utilizado Field:=2, ya que la columna del elemento es la segunda columna en nuestro conjunto de datos desde la izquierda.

Pero antes de mostrárselos, permítame cubrir primero algunos ejemplos para mostrarle todo lo que puede hacer el método AutoFiltro.

Haga clic aquí para descargar el archivo de ejemplo y seguirlo.

Ejemplo: Múltiples criterios (AND/OR) en la misma columna

Supongamos que tengo el mismo conjunto de datos, y esta vez quiero filtrar todos los registros en los que el elemento sea ‘Impresora’ o ‘Proyector’.

El siguiente código lo haría:

Sub FilterRowsOR()Worksheets("Sheet1").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", Operator:=xlOr, Criteria2:="Projector"End Sub

Note que aquí he utilizado el operador xlOR.

Esto le dice a VBA que utilice ambos criterios y filtre los datos si se cumple alguno de los dos criterios.

De manera similar, también puede utilizar el criterio AND.

Por ejemplo, si desea filtrar todos los registros en los que la cantidad es mayor que 10 pero menor que 20, puede utilizar el siguiente código:

Sub FilterRowsAND()Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _ Operator:=xlAnd, Criteria2:="<20"End Sub

Ejemplo: Múltiples criterios con diferentes columnas

Suponga que tiene el siguiente conjunto de datos.

Con el Autofiltro, puede filtrar múltiples columnas al mismo tiempo.

Por ejemplo, si desea filtrar todos los registros donde el artículo es ‘Impresora’ y el representante de ventas es ‘Mark’, puede utilizar el siguiente código:

Sub FilterRows()With Worksheets("Sheet1").Range("A1").AutoFilter field:=2, Criteria1:="Printer".AutoFilter field:=3, Criteria1:="Mark"End WithEnd Sub

Ejemplo: Filtrar los 10 mejores registros utilizando el método de autofiltrado

Suponga que tiene el siguiente conjunto de datos.

A continuación se muestra el código que le dará los 10 mejores registros (basado en la columna de cantidad):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10ItemsEnd Sub

En el código anterior, he utilizado ActiveSheet. Puede utilizar el nombre de la hoja si lo desea.

Nótese que en este ejemplo, si desea obtener los 5 primeros elementos, sólo tiene que cambiar el número en Criteria1:=»10″ de 10 a 5.

Así que para los 5 primeros elementos, el código sería:

Sub FilterRowsTop5()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="5", Operator:=xlTop10ItemsEnd Sub

Puede parecer extraño, pero no importa cuántos elementos principales desee, el valor del Operador siempre permanece xlTop10Items.

De forma similar, el siguiente código le daría los 10 elementos inferiores:

Sub FilterRowsBottom10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlBottom10ItemsEnd Sub

Y si quiere los 5 elementos inferiores, cambie el número en Criteria1:=»10″ de 10 a 5.

Ejemplo: Filtrar el 10 por ciento superior utilizando el método de autofiltrado

Suponga que tiene el mismo conjunto de datos (que se utilizó en los ejemplos anteriores).

A continuación se muestra el código que le dará los registros del 10 por ciento superior (basado en la columna de cantidad):

Sub FilterRowsTop10()ActiveSheet.Range("A1").AutoFilter Field:=4, Criteria1:="10", Operator:=xlTop10PercentEnd Sub

En nuestro conjunto de datos, ya que tenemos 20 registros, devolverá los 2 registros superiores (que es el 10% del total de registros).

Ejemplo: Uso de caracteres comodín en el autofiltro

Suponga que tiene un conjunto de datos como el que se muestra a continuación:

Si desea filtrar todas las filas en las que el nombre del elemento contiene la palabra ‘Board’, 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 ‘Board’ (que es el criterio).

Un asterisco puede representar cualquier número de caracteres. Así que esto filtraría cualquier elemento que tenga la palabra ‘board’ en él.

Ejemplo: Copiar filas filtradas en una nueva hoja

Si desea no sólo filtrar los registros basándose en criterios sino también copiar las filas filtradas, puede utilizar la siguiente macro.

Copia las filas filtradas, añade una nueva hoja de trabajo y luego pega estas filas copiadas en la nueva hoja.

Sub CopyFilteredRows()Dim rng As RangeDim ws As WorksheetIf Worksheets("Sheet1").AutoFilterMode = False ThenMsgBox "There are no filtered rows"Exit SubEnd IfSet rng = Worksheets("Sheet1").AutoFilter.RangeSet ws = Worksheets.Addrng.Copy Range("A1")End Sub

El código anterior comprobaría si hay filas filtradas en la Hoja1 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 base a un valor de celda

Usando el Autofiltro en VBA junto con una lista desplegable, se puede crear una funcionalidad en la que tan pronto como se selecciona un elemento del desplegable, se filtran todos los registros de ese elemento.

Algo como lo que se muestra a continuación:

Haga clic aquí para descargar el archivo de ejemplo y seguir.

Este tipo de construcción puede ser útil cuando se quiere filtrar rápidamente los datos y luego utilizarlos más adelante 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" Then If Range("B2") = "All" Then Range("A5").AutoFilter Else Range("A5").AutoFilter Field:=2, Criteria1:=Range("B2") End IfEnd IfEnd Sub

Este es un código de evento de la hoja de trabajo, que se ejecuta sólo cuando hay un cambio en la hoja de trabajo y la celda de destino es B2 (donde tenemos el desplegable).

Además, se utiliza una condición If Then Else para comprobar si el usuario ha seleccionado ‘Todo’ en el desplegable. Si se selecciona All, se muestra todo el conjunto de datos.

Este código NO se coloca en un módulo.

En cambio, es necesario colocarlo en el backend de la hoja de trabajo que tiene estos datos.

Aquí están los pasos para poner este código en la ventana de código de la hoja de trabajo:

1. Abra el Editor VB (atajo de teclado – ALT + F11).
2. En el panel del Explorador de Proyectos, haga doble clic en el nombre de la Hoja de Trabajo en la que desea esta funcionalidad de filtrado.
3. En la ventana de código de la hoja de trabajo, copie y pegue el código anterior.
4. Cierre el Editor VB.

Ahora, cuando utilice la lista desplegable, filtrará automáticamente los datos.

Este es un código de evento de la hoja de trabajo, que se ejecuta sólo cuando hay un cambio en la hoja de trabajo y la celda de destino es B2 (donde tenemos el desplegable).

Además, se utiliza una condición If Then Else para comprobar si el usuario ha seleccionado ‘Todo’ en el desplegable. Si se selecciona Todo, se muestra todo el conjunto de datos.

Activar/desactivar el autofiltro de Excel mediante VBA

Cuando se aplica el autofiltro a un rango de celdas, es posible que ya haya algunos filtros aplicados.

Puede utilizar el siguiente código para desactivar cualquier autofiltro preaplicado:

Sub TurnOFFAutoFilter() Worksheets("Sheet1").AutoFilterMode = FalseEnd Sub

Este código comprueba las hojas completas y elimina cualquier filtro que se haya aplicado.

Si no desea desactivar los filtros de toda la hoja, sino sólo de un conjunto de datos específico, utilice el código siguiente:

Sub TurnOFFAutoFilter() If Worksheets("Sheet1").Range("A1").AutoFilter Then Worksheets("Sheet1").Range("A1").AutoFilter End IfEnd Sub

El código anterior comprueba si ya hay filtros aplicados o no.

Si ya hay filtros aplicados, los elimina, si no, no hace nada.

De forma similar, si quiere activar el Autofiltro, utilice el siguiente código:

Sub TurnOnAutoFilter() If Not Worksheets("Sheet1").Range("A4").AutoFilter Then Worksheets("Sheet1").Range("A4").AutoFilter End IfEnd Sub

Comprobar si el Autofiltro ya está aplicado

Si tiene una hoja con múltiples conjuntos de datos y quiere asegurarse de que no hay filtros ya aplicados, puede utilizar el siguiente código.

Sub CheckforFilters()If ActiveSheet.AutoFilterMode = True ThenMsgBox "There are Filters already in place"ElseMsgBox "There are no filters"End IfEnd Sub

Este código utiliza una función de cuadro de mensaje que muestra un mensaje ‘There are Filters already in place’ cuando encuentra filtros en la hoja, de lo contrario muestra ‘There are no filters’.

Mostrar todos los datos

Si tienes filtros aplicados al conjunto de datos y quieres mostrar todos los datos, utiliza el siguiente código:

Sub ShowAllData()If ActiveSheet.FilterMode Then ActiveSheet.ShowAllDataEnd Sub

El código anterior comprueba si el FilterMode es TRUE o FALSE.

Si es verdadero, significa que se ha aplicado un filtro y utiliza el método ShowAllData para mostrar todos los datos.

Nota que esto no elimina los filtros. Los iconos de los filtros siguen estando disponibles para ser utilizados.

Utilización del Autofiltro en hojas protegidas

Por defecto, cuando se protege una hoja, los filtros no funcionan.

En caso de que ya tenga filtros aplicados, puede habilitar el Autofiltro para asegurarse de que funciona incluso en hojas protegidas.

Para ello, marque la opción Usar Autofiltro mientras protege la hoja.

Aunque esto funciona cuando ya tiene filtros en su lugar, en caso de que intente añadir Autofiltros utilizando un código VBA, no funcionará.

Como la hoja está protegida, no permitiría que ninguna macro se ejecutara y realizara cambios en el Autofiltro.

Así que necesitas usar un código para proteger la hoja de trabajo y asegurarte de que los autofiltros están habilitados en ella.

Esto puede ser útil cuando has creado un filtro dinámico (algo que cubrí en el ejemplo – ‘Filtrar datos basados en un valor de celda’).

Abajo está el código que protegerá la hoja, pero al mismo tiempo, le permitirá usar Filtros así como macros VBA en ella.

Private Sub Workbook_Open()With Worksheets("Sheet1").EnableAutoFilter = True.Protect Password:="password", Contents:=True, UserInterfaceOnly:=TrueEnd WithEnd Sub

Este código necesita ser colocado en la ventana de código de ThisWorkbook.

Aquí están los pasos para poner el código en la ventana de código ThisWorkbook:

1. Abra el Editor VB (atajo de teclado – ALT + F11).
2. En el panel del Explorador de Proyectos, haga doble clic en el objeto ThisWorkbook.
3. En la ventana de código que se abre, copie y pegue el código anterior.

En cuanto abra el libro de trabajo y active las macros, ejecutará la macro automáticamente y protegerá la Hoja1.

Sin embargo, antes de hacerlo, especificará ‘EnableAutoFilter = True’, lo que significa que los filtros funcionarán también en la hoja protegida.

Además, establece el argumento ‘UserInterfaceOnly’ a ‘True’. Esto significa que mientras la hoja de trabajo está protegida, el código de macros VBA seguiría funcionando.

También te pueden gustar los siguientes tutoriales VBA:

• Bucles VBA de Excel.
• Filtrar las celdas con el formato de fuente en negrita.
• Grabación de una macro.
• Ordenar los datos utilizando VBA.
• Ordenar las pestañas de la hoja de trabajo en Excel.