Ordenar automáticamente Rango o Tabla al calcular fórmula.

tonibcn · el 26 de octubre

Buenas tardes,

Primero de todo desear que, este desastre que estamos viviendo a nivel mundial, este os haya afectado lo menos posible, pediros precaución y responsabilidad.

Hasta hoy, no he necesitado de vuestra ayuda para mis peleas con Excel, por lo que, desde este preciso instante, os doy las gracias de forma anticipada por vuestra comprensión y ayuda al respecto de mis inquietudes. Os explico el problema, os pido disculpas por la extensión del tema, pero considero que cuanta más información mejor para que me podáis echar un cable:

Tengo un dashboard, no estándar, pero sí "armonizado" con el que mido una serie de indicadores sobre varias instalaciones en diferentes plantas productivas. La base de datos es bastante pesada por lo que estoy empezando a utilizar consultas para poder aligerarla un poco, estoy empezando, y seguro que podría optimizarla mucho más, pero bueno, paso a paso. Por el momento parece que no tiene mala pinta y no trabajo el tema consultas desde que utilizaba Access hace más de 20 años y he me he perdido completamente las evoluciones.

En el libro, tengo una serie de macros que me llaman a un archivo (base de datos) en un servidor determinado, me configura y descarga la nueva información en las tablas definidas en el dashboard, recalcula y ordena los rangos para los gráficos según unos criterios de ordenación (en algunos libros hasta 40 gráficos) y vuelve a recalcular nuevamente todo. Como he comentado anteriormente, esta base de datos es muy pesada (estamos hablando que, por semana y para una sola instalación son: ± 35000 registros en tres hojas, 1600 filas en una de las hojas de más datos) Esto ocasiona que, semana a semana se vayan ralentizando los cálculos y, sobre todo, una vez que la macro ordena según los diferentes criterios, pues los ocho procesadores de mi máquina se pueden tirar, en algún caso, hasta cuarenta minutos calculando. Sé que, para este volumen de datos, Excel no es la mejor opción, pero aún estoy muy pez con Power Bi y no encuentro el tiempo para avanzar, además de la dificultad añadida de no poder configurar tablas en columnas, si no en filas. Con las consultas le he conseguido quitar unas 19 columnas con fórmulas que multiplicas por las miles de filas, optimizo bastante el cálculo, pero no es suficiente.

En la antigua macro, lo que iba haciendo era: seleccionar cada uno de los rangos a ordenar (C13:K122) y (C128:K237) hay más rangos, pero en el ejemplo solo he incluido estos dos, y ordenar según un criterio (Mayor a Menor), ahora quería que esto se hiciera de forma automática cuando, al introducir nuevos registros de base de datos y se recalcule la fórmula incluida en la columna de ordenación (TOTAL PERIODO) se ordenen de mayor a menor dichos rangos. Con el procedimiento “Change” he conseguido que, al introducir algún valor de forma manual en la columna TOTAL PERIODO, el rango se ordene y chuta bien, pero el objetivo es el indicado anteriormente, que se ordene nuevamente el rango al variar el resultado de la fórmula.

Lo he probado cambiando el procedimiento a “Calculate” pero me da un error de compilación por lo que la variable definida seguro que no es la correcta. El código que, incluyo directamente en la hoja de trabajo, es el siguiente:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 11 Then

With Range("C12:K122")

.Sort key1:=.Cells(1, 9), order1:=xlDescending, Header:=xlYes

End With

End If

If Target.Column = 11 Then

With Range("C127:K237")

.Sort key1:=.Cells(1, 9), order1:=xlDescending, Header:=xlYes

End With

End If

End Sub

Ya de paso, si me podéis aconsejar sobre cómo agilizar los cálculos o como estructurar las consultas, sería capaz de haceros la ola… Desactivo el cálculo automático al inicio de la macro, desactivo los filtros que pudiera haber activos, el refresco de pantalla en “False” hasta el final de la macro, delimito el espacio de trabajo de las hojas, delimito el rango de los gráficos de evolución a un máximo de diez modos de incidencia y solo recalculo en dos ocasiones en todo el proceso.

Reitero mi agradecimiento y aprovecho para felicitar a todos los integrantes de esta extraordinaria comunidad, que es la biblia de los sufridores de Excel, y de la que me gustaría ser parte activa para lo que podáis necesitar, por lo que quedo a vuestra entera disposición.

Recibid un cordial saludo.

P.D. En el fichero de ejemplo os he incluido solo algunas columnas de la base de datos y un par de rangos a ordenar, si necesitáis el archivo completo no dudéis en solicitármelo. El código está en la Hoja "Rango prueba 1"

ORDEN AUTOMATICO RANGO BETA v01.xlsm

John Jairo V · el 27 de octubre

¡Hola @tonibcn!

Puedes usar el siguiente código, y modificarlo de acuerdo con tus rangos:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim a, r, i&
  
  a = Array("G13:J122", "G128:J237")
  r = Array("C12:K122", "C127:K237")
  For i = 0 To UBound(a)
    If Not Intersect(Target, Range(a(i))) Is Nothing Then
      With Range(r(i))
        .Sort .Cells(1, 9), xlDescending, Header:=xlYes
      End With
    End If
  Next i
End Sub

¡Comentas! ¡Bendiciones!

tonibcn · el 27 de octubre

Buenos días,

Voy a echar un vistazo y os digo algo, el tiempo de tomarme un café para ver si me despierto.

Muchas gracias por la rápida respuesta.

tonibcn · el 27 de octubre

Buenos días de nuevo John.

No funciona, no hace nada, manteniendo el procedimiento en Change, al recalcular se queda igual y si lo cambio a Calculate, me vuelve a dar el error de compilación por la variable.

Muchas gracias de nuevo por la intención pero creo que debemos seguir investigando.

Antoni · el 27 de octubre

Puedes utilizar cualquiera de las 2 macros (O una o la otra, no las dos a la vez)

Private Sub Worksheet_Change(ByVal Target As Range): On Error Resume Next
'------------------------
'En la hoja Base de datos <--------------------------------------------
'------------------------
If Not Intersect(Target, Range("C:C")) Is Nothing Then
   With Sheets("Rango prueba 1")
      Application.EnableEvents = False
      .Range("C12:K122").Sort Key1:=.Columns("K"), Order1:=xlDescending, Header:=xlYes
      .Range("C127:K237").Sort Key1:=.Columns("K"), Order1:=xlDescending, Header:=xlYes
      Application.EnableEvents = True
   End With
End If
End Sub

Private Sub Worksheet_Calculate(): On Error Resume Next
'-------------------------
'En la hoja Rango prueba 1 <-------------------------------------------
'-------------------------
Application.EnableEvents = False
Range("C12:K122").Sort Key1:=Columns("K"), Order1:=xlDescending, Header:=xlYes
Range("C127:K237").Sort Key1:=Columns("K"), Order1:=xlDescending, Header:=xlYes
Application.EnableEvents = True
End Sub

tonibcn · el 27 de octubre

Muchas gracias Antoni, lo pruebo y te digo.

¡Salut...!

Antoni · el 27 de octubre

Hace 17 minutos , tonibcn dijo:

Muchas gracias Antoni, lo pruebo y te digo.

¡Salut...!

i força al canut....?

tonibcn · el 27 de octubre

Antes de exponer las conclusiones, agradecer a John y Antoni su tiempo y conocimiento en la resolución del problema.

Antoni, las dos soluciones aportadas han funcionado como un "tiro". A mayores, la segunda opción funciona tanto, si la modificación se realiza a través del recálculo con fórmulas, como si dicha modificación de la columna TOTAL HORAS se realiza de forma manual con introducción directa del dato.

Sois unos Crack... Muchas, muchísimas gracias.

Desde ya un nuevo Fan.

Mes salut i força ?