Ordenar Datos en Excel VBA

Ordenar Datos con Excel VBA

Excel tiene un excelente medio para ordenar un rango de datos tabulares usando la cinta de opciones en el front-end de Excel, y en algún momento, probablemente querrá usar esta funcionalidad dentro de su código VBA. Afortunadamente, esto es muy fácil de hacer.

El cuadro de diálogo del front-end se encuentra haciendo clic en el icono «Ordenar» del grupo «Ordenar y filtrar» de la pestaña «Datos» de la cinta de Excel. Primero debe seleccionar un rango de datos tabulares.

También puedes utilizar Alt-D-O-2 para mostrar el cuadro de diálogo de una ordenación personalizada.

El método de ordenación se ha mejorado mucho en versiones posteriores de Excel. La ordenación solía estar restringida a tres niveles, pero ahora puedes introducir tantos niveles como necesites, y esto también se aplica dentro de VBA.

Puede incorporar todas las funciones de ordenación que se ofrecen en el cuadro de diálogo de ordenación de Excel en su código VBA. La función de ordenación en Excel es rápida, y más rápida que cualquier cosa que pueda escribir usted mismo en VBA, así que aproveche la funcionalidad.

Tenga en cuenta que cuando hace una ordenación en VBA, los parámetros de ordenación siguen siendo los mismos en el cuadro de diálogo de ordenación frontal. También se guardan cuando se guarda el libro de trabajo.

Si un usuario selecciona el mismo rango de datos tabulares y hace clic en el icono de ordenación, verá todos los parámetros introducidos por su código VBA. Si quieren hacer una ordenación de su propio diseño, tendrán que borrar primero todos tus niveles de ordenación, lo que será muy molesto para ellos.

Además, si usted no cambia los parámetros dentro de su código, y confía en los valores por defecto, puede encontrar que el usuario ha hecho cambios que se reflejarán en su ordenación VBA, y puede dar resultados inesperados, que pueden ser muy difíciles de depurar.

Afortunadamente, hay un método claro en VBA para reajustar todos los parámetros de ordenación para que el usuario vea un cuadro de diálogo de ordenación limpio

Worksheets("Hoja1").Sort.SortFields.Clear

Es una buena práctica borrar los parámetros de ordenación en VBA antes y después de completar la ordenación.

Uso Práctico del Método de Ordenación en VBA

Cuando se importan datos tabulares a Excel, a menudo se encuentran en un orden muy aleatorio. Pueden importarse desde un archivo CSV (valores separados por comas) o pueden proceder de un enlace a una base de datos o a una página web. No se puede confiar en que estén en un orden fijo de una importación a otra.

Si presentas estos datos a un usuario dentro de tu hoja de cálculo, puede que le resulte difícil mirar y entender una gran cantidad de datos que, en términos de orden, están por todas partes. Es posible que quiera agrupar los datos o cortar y pegar ciertas secciones en otra aplicación.

También puede querer ver, por ejemplo, el empleado mejor pagado, o el empleado con más antigüedad.

Utilizando el método de ordenación en VBA, puede ofrecer opciones que permitan una fácil ordenación para el usuario.

Datos de Ejemplo para Demostrar la Ordenación de Excel con VBA

Primero necesitamos introducir algunos datos de ejemplo en una hoja de trabajo, para que el código pueda demostrar todas las facilidades disponibles en VBA.

 

Copie estos datos en una hoja de trabajo (llamada ‘Hoja1’) exactamente como se muestra.

Observe que se han utilizado diferentes colores de fondo de las celdas y de las fuentes, ya que éstos también pueden utilizarse como parámetros de ordenación. La ordenación mediante los colores de las celdas y las fuentes se demostrará más adelante en el artículo. Observe también que en la celda E3, el nombre del departamento está en minúsculas.

No necesita el interior de la celda y los colores de la fuente si no desea utilizar los ejemplos de ordenación por celda y color de la fuente.

Grabación de una Macro para Ordenar con VBA

El código VBA para la ordenación puede ser bastante complicado, y a veces puede ser una buena idea hacer la ordenación en el frente de Excel y grabar una macro para mostrarle cómo funciona el código.

Desafortunadamente, la función de grabación puede generar una enorme cantidad de código porque establece prácticamente todos los parámetros disponibles, aunque los valores por defecto de muchos parámetros son aceptables para su operación de ordenación.

Sin embargo, te da una muy buena idea de lo que implica escribir código de ordenación VBA, y una ventaja es que el código grabado siempre te funcionará. Su propio código puede necesitar pruebas y depuración para que funcione correctamente.

Recuerde que para una operación realizada en VBA, no hay función de deshacer, por lo que es una buena idea hacer una copia de los datos tabulares en otra hoja de trabajo antes de empezar a escribir su código de ordenación.

Como ejemplo, si usted hiciera una ordenación simple en los datos de muestra anteriores, ordenando por Empleado, la grabación generaría el siguiente código:

Sub Macro1()

Range("A1:E6").Select

ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Hoja1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Este es un trozo de código bastante grande, y mucho de él es innecesario debido a los parámetros por defecto que se utilizan. Sin embargo, si usted está bajo presión de tiempo para completar un proyecto y necesita algún código rápidamente que funcione, puede pegar fácilmente esto en su propio código VBA.

No obstante, si quiere que su código sea comprensible y más elegante, hay otras opciones disponibles.

Código VBA Para Hacer Una Ordenación de Un Solo Nivel

Si quiere ordenar el código de ejemplo basándose sólo en Empleado como antes al grabar una macro, el código es muy sencillo:

Sub OrdenUnSoloNivel()

Worksheets("Hoja1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("A1"), Header:=xlYes

End Sub

Esto es mucho más fácil de entender que el código grabado porque acepta los valores por defecto, por ejemplo, ordenar de forma ascendente, por lo que no es necesario establecer los parámetros a valores por defecto. Esto supone que se ha utilizado una sentencia ‘Clear’ de antemano.

El método ‘Clear’ se utiliza inicialmente para asegurar que todos los parámetros de ordenación de esa hoja de trabajo vuelvan a los valores por defecto. Un usuario puede haber establecido previamente los parámetros a valores diferentes, o una ordenación anterior en VBA puede haberlos cambiado. Es importante comenzar desde una posición por defecto cuando se ordena, de lo contrario podría terminar fácilmente con resultados incorrectos.

El método Clear no restablece el parámetro Header, y es aconsejable incluirlo en su código, de lo contrario Excel podría intentar adivinar si hay una fila de encabezado o no.

Ejecute este código contra los datos de muestra y su hoja de trabajo tendrá el siguiente aspecto:

 

Código VBA Para Realizar una Ordenación Multinivel

Puedes añadir tantos niveles de ordenación como necesites dentro de tu código. Supongamos que desea ordenar primero por departamento y luego por fecha de ingreso, pero en orden ascendente para el departamento y descendente para la fecha de inicio:

Sub OrdenMultinivel()

    Worksheets("Hoja1").Sort.SortFields.Clear
    
    Range("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
        Order1:=xlAscending, Order2:=xlDescending

End Sub

Observe que ahora hay dos Key (claves) en la sentencia de ordenación (Key1 y Key2). La Key1 (columna E del departamento) se ordena en primer lugar y luego la Key2 (columna C de la fecha de ingreso) se ordena en base a la primera ordenación.

También hay dos parámetros de orden. Order1 se asocia con la Key1 (Departamento) y Order2 se asocia con la Key2 (Fecha de ingreso). Es importante asegurarse de que las Key y los Orden se mantengan sincronizados entre sí.

Ejecute este código contra los datos de muestra y su hoja de trabajo se verá así:

La columna Departamento (E) está en orden ascendente, y la columna Fecha de ingreso (C) está en orden descendente.

El efecto de esta ordenación es más evidente cuando se observa a Jane Halfacre (fila 3) y John Sutherland (fila 4). Ambos están en Finanzas, pero Jane Halfacre empezó después que John Sutherland y las fechas aparecen en orden descendente.

Si el rango de datos tabulares puede ser de cualquier longitud, puede utilizar el objeto UsedRange para definir el rango de ordenación. Esto sólo funcionará si sólo hay datos tabulares en la hoja de trabajo, ya que cualquier valor fuera de los datos dará resultados incorrectos para el número de filas y columnas.

Sub OrdenMultinivel_2()

    Worksheets("Hoja1").Sort.SortFields.Clear
    Worksheets("Hoja1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
        Order1:=xlAscending, Order2:=xlDescending

End Sub

Esto evita el problema si se utiliza el método ‘End(xlDown)’ para definir el rango de ordenación. Si hay una celda en blanco en medio de los datos, todo lo que esté después de la celda en blanco no se incluirá, mientras que UsedRange baja hasta la última celda activa de la hoja de trabajo.

Ordenar por Color de Celda

Desde Excel 2007, es posible ordenar por el color de fondo de una celda, lo que proporciona una enorme flexibilidad a la hora de diseñar el código de ordenación en VBA.

Sub OrdenarPorColorDeCeldaUnNivel()

    Worksheets("Hoja1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
        SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets("Hoja1").Sort
        .SetRange Range("A2:E6")
        .Apply
    End With

End Sub

Este código ordenará el rango de datos de muestra (A2:A6) basándose en el color de fondo de la celda. Tenga en cuenta que ahora hay un parámetro adicional llamado ‘SortOn’ que tiene el valor de ‘xlSortOnCellColor’.

Tenga en cuenta que el parámetro ‘SortOn’ sólo puede ser utilizado por un objeto de hoja de cálculo y no por un objeto de rango.

Debido a esto el código es más complicado que para una ordenación utilizando valores de celda.

Este código utiliza un valor clave para la ordenación que cubre todo el rango de datos, pero puede especificar columnas individuales como clave para la ordenación del color de fondo, y utilizar múltiples niveles como se ha mostrado anteriormente.

Después de ejecutar este código, su hoja de cálculo tendrá este aspecto:

 

Ordenar por Color de Fuente

La función de ordenación en Excel VBA ofrece aún más flexibilidad, ya que puede ordenar por colores de fuente:

Sub OrdenarPorColorDeFuenteUnNivel()

    Worksheets("Hoja1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add(Range("A2:A6"), _
        xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)
    
    With ActiveWorkbook.Worksheets("Hoja1").Sort
        .SetRange Range("A1:E6")
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With

End Sub

El código para ordenar por el color de la fuente es mucho más complicado que para el color de fondo de la celda. El parámetro ‘SortOn’ ahora tiene el valor de ‘xlSortOnFontColor’.

Tenga en cuenta que debe especificar la orientación como ‘xlTopToBottom’ y debe especificar un color para ordenar. Esto se especifica en términos RGB (Rojo, Verde, Negro) con valores que van de 0 a 255.

Después de ejecutar este código con los datos de muestra, su hoja de cálculo tendrá este aspecto:

La ordenación mediante colores en VBA es mucho más complicada que la ordenación multinivel, pero si su código de ordenación no funciona (lo que puede ocurrir si falta un parámetro o no ha introducido el código correctamente), siempre puede recurrir a la grabación de una macro e integrar el código grabado en su VBA.

Uso de Otros Parámetros en la Ordenación VBA

Hay una serie de parámetros opcionales que puede utilizar en su código VBA para personalizar su ordenación.

SortOn

SortOn elige si la ordenación utilizará valores de celda, colores de fondo de celda o colores de fuente de celda. La configuración por defecto es Valores de Celda.

SortOn = xlSortOnValues

Order

Order elige si la ordenación se hará en orden ascendente o descendente. El valor por defecto es Ascendente.

Order = xlAscending

DataOption

DataOption elige cómo se ordenan el texto y los números. El parámetro xlSortNormal ordena los datos numéricos y de texto por separado. El parámetro xlSortTextAsNumbers trata el texto como datos numéricos para la ordenación. El valor por defecto es xlSortNormal.

DataOption = xlSortNormal

Header

Cabecera elige si el rango de datos tabulares tiene una fila de cabecera o no. Si hay una fila de cabecera, no quiere que se incluya en la ordenación.

Los valores de los parámetros son xlYes, xlNo, and xlYesNoGuess. xlYesNoGuess deja que Excel determine si hay una fila de cabecera, lo que podría llevar fácilmente a resultados inconsistentes. No se recomienda el uso de este valor.

El valor por defecto es xlNo (no hay fila de cabecera dentro de los datos). Con los datos importados, suele haber una fila de cabecera, así que asegúrese de establecer este parámetro en xlYes.

Header = xlYes

MatchCase

Este parámetro determina si la ordenación distingue entre mayúsculas y minúsculas o no. Los valores opcionales son True o False. Si el valor es Falso, entonces los valores en minúsculas se consideran igual que los valores en mayúsculas. Si el valor es True, entonces la ordenación mostrará la diferencia entre los valores en mayúsculas y minúsculas dentro de la ordenación. El valor por defecto es Falso.

MatchCase = False

Orientation

Este parámetro determina si la ordenación se realizará hacia abajo a través de las filas, o a través de todas las columnas. El valor por defecto es xlTopToBottom (ordenar a través de las filas). Puede utilizar xlLeftToRight si desea ordenar horizontalmente. Valores como xlRows y xlColumns no funcionan para este parámetro.

Orientación = xlTopToBottom

SortMethod

Este parámetro sólo se utiliza para ordenar los idiomas chinos. Tiene dos valores, xlPinYin y xlStroke. xlPinYin es el valor por defecto.

xlPinYin ordena utilizando el orden fonético chino para los caracteres. xlStroke ordena por la cantidad de trazos en cada carácter.

Si graba una macro de ordenación, este parámetro siempre se incluirá en el código, y es posible que se pregunte qué significa. Sin embargo, a no ser que se trate de datos en chino, es de poca utilidad.

SortMethod = xlPinYin

Uso de un Evento de Doble Clic para Ordenar Datos Tabulares

En toda la funcionalidad que Microsoft incluyó en los métodos de ordenación para VBA, no incluyó un medio sencillo para hacer doble clic en la cabecera de una columna y ordenar la totalidad de los datos tabulares basándose en esa columna concreta.

Esta es una característica realmente útil, y es fácil escribir el código para hacerlo.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Se asume que los datos comienzan en la celda A1

'Crea tres variables para capturar la columna objetivo seleccionada y la columna y fila máxima de _
' los datos tabulares
Dim Col As Integer, RCol As Long, RRow As Long

'Comprobar que el usuario ha hecho doble clic en la fila de cabecera - fila 1 en caso contrario salir del sub
If Target.Row <> 1 Then Exit Sub

'Capturar el máximo de filas en el rango de datos tabulares usando el objeto 'UsedRange'
RCol = ActiveSheet.UsedRange.Columns.Count

'Capturar el máximo de columnas en el rango de datos tabulares utilizando el objeto 'UsedRange'
RRow = ActiveSheet.UsedRange.Rows.Count

'Comprobar que el usuario no ha hecho doble clic en una columna fuera del rango de datos tabulares
If Target.Column > RCol Then Exit Sub

'Captura la columna sobre la que el usuario ha hecho doble clic
Col = Target.Column

'Borrar los parámetros de ordenación anteriores
ActiveSheet.Sort.SortFields.Clear

'Ordenar el rango tabular según lo definido por las filas y columnas máximas del objeto 'UsedRange'
'Ordenar los datos tabulares utilizando la columna sobre la que el usuario ha hecho doble clic como clave de ordenación
ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, Col), Header:=xlYes

'Seleccione la celda A1 - esto es para asegurar que el usuario no se queda en modo de edición después de la ordenación es _
' completado
ActiveSheet.Range("A1").Select

End Sub

Este código debe colocarse en el evento de doble clic en la hoja que contiene los datos tabulares. Para ello, haga clic en el nombre de la hoja de cálculo en la ventana del Explorador de Proyectos (esquina superior izquierda de la pantalla del VBE) y, a continuación, seleccione «Worksheet» en el primer menú desplegable de la ventana de código. Seleccione ‘BeforeDoubleClick’ en el segundo desplegable, y entonces podrá introducir su código.

Tenga en cuenta que no hay nombres, rangos o referencias de celdas codificadas en este código, excepto para mover el cursor a la celda A1 al final del código. El código está diseñado para obtener toda la información necesaria a partir de las coordenadas de la celda en la que el usuario ha hecho doble clic y el tamaño del rango de datos tabulares.

No importa el tamaño del rango de datos tabulares. El código seguirá recogiendo toda la información necesaria y puede utilizarse en los datos de cualquier lugar del libro de trabajo sin tener que codificar los valores.

La única suposición que se hace es que hay una fila de cabecera en los datos tabulares, y que el rango de datos comienza en la celda A1, pero la posición inicial del rango de datos puede cambiarse fácilmente dentro del código.

Cualquier usuario quedará convenientemente impresionado con esta nueva funcionalidad de ordenación!

 

Ampliación de la Función de Ordenación Mediante VBA

Microsoft ha permitido una enorme flexibilidad en la ordenación utilizando una amplia gama de parámetros. Sin embargo, dentro de VBA, se puede llevar esto más allá.

Supongamos que quiere ordenar cualquier valor con una fuente en negrita en la parte superior de sus datos. No hay manera de hacer esto en Excel, pero puede escribir el código VBA para hacerlo:

Sub OrdenarPorNegrita()

'Crear variables para mantener el número de filas y columnas de los datos tabulares
Dim RRow As Long, RCol As Long, N As Long

'Desactivar la actualización de la pantalla para que el usuario no pueda ver lo que está sucediendo - pueden ver _
' los valores que se modifican y se preguntan por qué
Application.ScreenUpdating = False

'Capturar el número de columnas en el rango de datos tabulares
RCol = ActiveSheet.UsedRange.Columns.Count

'Capturar el número de filas dentro del rango de datos tabulares
RRow = ActiveSheet.UsedRange.Rows.Count

'Iterar a través de todas las filas del rango de datos tabulares ignorando la fila de cabecera
For N = 2 To RRow
    'Si una celda tiene una fuente en negrita, coloque un valor 0 a la izquierda del valor de la celda
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).Value
    End If
Next N

'Borrar cualquier parámetro de ordenación anterior
ActiveSheet.Sort.SortFields.Clear

'Ordenar el rango de datos tabulares. Todos los valores con un valor 0 a la cabeza se moverán a la parte superior
ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, 1), Header:=xlYes

'Iterar por todas las filas del rango de datos tabulares ignorando la fila de cabecera
For N = 2 To RRow
    'Si una celda tiene una fuente en negrita, entonces elimina el valor 0 inicial del valor de la celda para _
    ' restaurar los valores originales
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)
    End If
Next N

'Vuelva a activar la actualización de la pantalla
Application.ScreenUpdating = True

End Sub

El código calcula el tamaño del rango de datos tabulares utilizando el objeto ‘UsedRange’ y luego itera a través de todas las filas dentro de él. Cuando se encuentra una fuente en negrita, se coloca un cero a la izquierda delante del valor de la celda.

A continuación se realiza una ordenación. Como la ordenación es ascendente, todo lo que tenga un cero delante irá al principio de la lista.

El código entonces itera a través de todas las filas y elimina los ceros iniciales, restaurando los datos a sus valores originales.

Este código ordena utilizando las fuentes en negrita como criterio, pero podría utilizar fácilmente otras características de las celdas de la misma manera, por ejemplo, la fuente en cursiva, el tamaño de punto del texto, la fuente subrayada, el nombre de la fuente, etc.