Hola, 

Tengo un excel en el trabajo y cada vez que copio y pego valores tarda más de 3 minutos en desaparecer el círculo azul de Microsoft. 

¿Sabeis como hacer para que la velocidad de cálculo aumente?

He leido que borrar el exceso de formato ayuda. Estoy utilizando la opcion de borrar el exceso de formato. Sin embargo, selecciono Control + Fin y la celda activa es XFD430. La última celda con contenido es la GJ426.  Por ello, creo que no está funcionando el borrado de exceso de formato.  ¿Sabéis a que se debe?

 

 

Hola @javierc

Revisa bien el archivo, estoy casi seguro que notarás un tamaño excesivamente grande (en megabytes), son muchas las causas y los efectos son archivos grandes y lentos.

Cosas a revisar:

• Eliminar formatos condicionales excesivos pues son tratados como cálculos volátiles
• Minimizar el uso de fórmulas volátiles (o activar el cálculo manual)
• Revisar que no haya estilos excesivos en el archivo
• Revisar que no hayan "imágenes" excesivas. He visto muchos que tienen miles de imágenes prácticamente invisibles de 1 pixel x 1 pixel y no saben que las tienen (no sé como duplican miles de imágenes sin darse cuenta)
• Eliminar filas/columnas más allá de los límites de tu cuadro, si no usas hasta la columna XFD sino solo hasta GJ, entonces elimina las columnas desde GK hasta XFD.

Gracias @DiegoPC

 

Elimino las columnas y tras ello hago control + Fin para ver la última celda activa. Me sigue llevando a la columna XFD.

¿Qué otra forma hay para eliminar las celdas fuera del cuadro de trabajo? Parece que no funciona.

Por otra parte, el archivo tiene contraseña, ¿afecta al archivo?

El archivo lo he heredado y no veo que haya imágenes. 

 ¿Hay alguna herramienta que las localice, similar a buscar?

Gracias de antemano.

Gracias @DiegoPC

 

Elimino las columnas y tras ello hago control + Fin para ver la última celda activa. Me sigue llevando a la columna XFD.

He probado con una hoja nueva. He escrito en la celda L9 y después he eliminado fila y celda. Sin embargo, al hacer control+fin me sigue mostrado la celda L9 en lugar de I6. 

 

¿Qué otra forma hay para eliminar las celdas fuera del cuadro de trabajo? Parece que no funciona.

Por otra parte, el archivo tiene contraseña, ¿afecta al archivo?

El archivo lo he heredado y no veo que haya imágenes. 

 ¿Hay alguna herramienta que las localice, similar a buscar?

Gracias de antemano.

Sube tu archivo. Si sobrepasa el tamaño permitido, súbelo a la nube. (Dropbox, SkyDrive, Mega,.....)

 

Gracias Antoni. 

Lamentablemente no puedo subirlo, es información de la empresa y me jugaría el despido nada más llegar.

Haz tu proceso de Copiado y pegado pero primero ponlo en Formato - Recalculo Manual y al terminar de hacer sub cambios o correcciones vuelve a dar

Formato - Recalculo Automático o con la Tecla F9

Hola

La combinación CTRL + FIN te lleva a la última celda usada (aunque borres el contenido), para que se "refresque" la última celda usada es necesario guardar el archivo.

Para que no te lleve a la XFD elimina las columnas desde la GK hasta XFD (asegúrate de seleccionar las columnas completas) y luego debes guardar el archivo, eso te llevará solo hasta la GJ cuando presiones CTRL + FIN. Si aun así no funciona, primero borra formatos de todas esas columas y luego guarda (podría ser que tu antecesor aplicó formato a absolutamente toda la hoja)

Las imágenes las puedes ver gracias al panel de selección. He visto muchas veces archivos con cientos de imágenes, que prácticamente son imágenes diminutas y de color blanco, lo que hace que no sean detectadas a simple vista pero que allí están. Los autores de esos archivos no se habían dado cuenta de eso y no sabían cómo podrían haberlo logrado.

No importa si tu archivo tiene contraseña, eso no afecta.

Dale una revisada también a los estilos del libro.

21 hours ago, DiegoPC said:

Hola

La combinación CTRL + FIN te lleva a la última celda usada (aunque borres el contenido), para que se "refresque" la última celda usada es necesario guardar el archivo.

Para que no te lleve a la XFD elimina las columnas desde la GK hasta XFD (asegúrate de seleccionar las columnas completas) y luego debes guardar el archivo, eso te llevará solo hasta la GJ cuando presiones CTRL + FIN. Si aun así no funciona, primero borra formatos de todas esas columas y luego guarda (podría ser que tu antecesor aplicó formato a absolutamente toda la hoja)

Las imágenes las puedes ver gracias al panel de selección. He visto muchas veces archivos con cientos de imágenes, que prácticamente son imágenes diminutas y de color blanco, lo que hace que no sean detectadas a simple vista pero que allí están. Los autores de esos archivos no se habían dado cuenta de eso y no sabían cómo podrían haberlo logrado.

No importa si tu archivo tiene contraseña, eso no afecta.

Dale una revisada también a los estilos del libro.

Muchas gracias.

He borrado las columnas más de 3 veces en varias hojas y me sigue llevando al final.

Respecto al tema de las fotos, he seguido los pasos  que me indicastey me sale un comentario 1, pero no sé como borrarlo. Agradecería que me guiaras con ello.

Por último, me gustaría saber cómo reviso los estilos de libro

 

Gracias de antemano

Es extraño lo que te sucede con las columnas, asegúrate de seleccionar absolutamente toda la columna usando los encabezados (las letras del área ploma donde están las letras)

Si solo hay un comentario entonces no creo que eso lo esté lenteando.

Los estilos los revisas en Inicio, en el grupo Estilos. También puedes hacerlo en la ventana de inmediato del editor VBE con el comando 

?Thisworkbook.Styles.Count

Si te salen demasiados (más de 150) podría ser eso lo que falla.

Muchas gracias, revisaré los estilos.

Respecto a borrar el exceso de columnas, selecciono los encabezados. 

Probé con un archivo nuevo y escribí en las celdas J8 y X13. Borré las columna posteriores a J y al hacer control +fin, me seleccionaba la celda X13, incluso tras guardarlo. Por ello, no es algo que suceda solo en mi archivo. 

Hola @DiegoPC

Tengo más de 900 estilos. 

¿Cómo puedo saber qué estilos no se están usando?

¿Hay alguna opción para ver que celdas usan un determinado estilo?

 

Gracias de antemano,

Hola Javier

Te he preparado una pequeña macro que analizará qué estilos tienes y te dirá en cuántas celdas se está usando cada estilo. La macro podría ser demasiado lenta si tu archivo tiene muchísimas hojas y celdas usadas pero podría ser de utilidad.

Copia el código en el editor de macros y a ejecutar... no importa en qué libro pegues el código porque siempre trabajará con el libro activo.

Sub Estilos()
    On Error Resume Next
    
    Dim Hoja As Worksheet
    Dim H As Worksheet
    Dim C As Range
    Dim Estilo As Style
    Dim Fila As LongPtr
    Dim Contador As LongPtr
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ActiveWorkbook.Worksheets("Estilos").Delete
    Set Hoja = ActiveWorkbook.Worksheets.Add
    Hoja.Name = "Estilos"
    Hoja.[A1].Value = "Nombre"
    Hoja.[B1].Value = "Tipo"
    Hoja.[C1].Value = "Celdas"
    
    Fila = 2
    For Each Estilo In ActiveWorkbook.Styles
        Hoja.Range("A" & Fila).Value = Estilo.Name
        Hoja.Range("B" & Fila).Value = IIf(Estilo.BuiltIn, "Integrado", "Personalizado")
        Contador = 0
        For Each H In ActiveWorkbook.Worksheets
            For Each C In H.UsedRange
                If C.Style = Estilo.Name Then Contador = Contador + 1
            Next
        Next
        Hoja.Range("C" & Fila).Value = Contador
        Fila = Fila + 1
    Next
    
    Hoja.[A1].CurrentRegion.Columns.AutoFit
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

Hola Diego,

 

He  dejado la Excel trabajando durante toda la noche y no se ha terminado. 

Quería preguntarte si es posible segmentar la macro para reducir el tiempo. 

Tengo unas 20 hojas.

 

Gracias de antemano,

Hola Javier

Tus archivos dan miedo!!! jajajaja. Bueno, hice una modificación para que tengas un mayor control y calcules solo la parte que quieras calcular. Obviamente que requiere más cosas de tu parte. El funcionamiento es el siguiente:

1. Abre el archivo del terror que contiene tus 20 hojas y los tres millones de estilos. ?
2. Descarga el adjunto y ábrelo también.
3. Haces click en el primer botón de arriba a la izquierda y seleccionas tu libro.
4. Haces click en el segundo botón y se cargarán los estilos existentes en tu libro del terror.
5. Haces click en el tercer botón y se cargarán las hojas existentes en tu libro del terror. Hasta este paso todo es rapidito.
6. Ahora viene la parte demorona y que puedes hacer por partes: Selecciona únicamente las celdas que quieras calcular (intersecciones de nombre de estilo VS nombre de hoja) y con esas celdas seleccionadas simplemente haces un CLICK DERECHO, eso calculará cuántas celdas de esa hoja tienen ese estilo.

Felicidades! Ya tienes tarea para las siguientes dos semanas ?

Revisa Estilos.xlsm

jaja. Gracias Diego. 

El fin de semana lo probaré.

Eres fantástico.

Como comenta Janlui evita que con cada dato que introduzcas recalcule todo. eso se puede hacer incluyendo

        Application.Calculation = xlCalculationManual

pero tendrás que incluir

        Calculate

cada vez que quieras utilizar los valores modificados. Si tu macro trabaja con varias hojas  y muchos datos en ella cada vez que incluyes un nuevo dato recalcula todo, lo que lleva cierto tiempo, por ello solo utilices"Calculate" solo cuando tu macro utilice un valor modificado por los uktimos datos , esto reduce el tiempo de cálculo dráasticamente

Si quiere que al final quede en forma de cálculo automático deberças incluir

       Application.Calculation = xlCalculationAutomatic  

Hola Diego,

 

He intentado hacer tu guía pero me sale este mensaje tras seleccionar cargar estilos:

 

Si selecciono debug, me sale esta flecha en la macro:

 

Qué puedo hacer?

Gracias de antemano.

Hola. 

 

Se me ha ocurrido que puedo compartir el archivo si borro el contenido. Al menos así podréis ayudarme con el tema de los estilos.

https://www.dropbox.com/scl/fi/xb4r8minho4fpqbr3nucz/Modelo.xlsx?dl=0&rlkey=yi28s2r19shkdp1kb64vix9gi

Espero que os sirva!

Gracias de antemano.

Hace 51 minutos , javierc dijo:

Hola Diego,

 

He intentado hacer tu guía pero me sale este mensaje tras seleccionar cargar estilos:

 

Si selecciono debug, me sale esta flecha en la macro:

 

Qué puedo hacer?

Gracias de antemano.

Hola Javier

Veo que tu archivo tiene 1085 estilos, el nombre de algunos de ellos empieza con el signo igual o con el signo de adición y cuando la macro intenta poner el nombre del estilo en las celdas de la columna A salta el error. Para evitarlo, modifica la línea amarilla por esta otra

 

Range("A10").Offset(Contador, 0).Value = Chr(39) & Estilo.Name

Eso evitará que los estilos con nombre extravagante causen error en la macro.

Otra cosa muy importante es que veo que la última celda usada en varias hojas está en la XFD... eso hace que tu archivo sea de 37 MB estando vacío. Siempre ten mucho cuidado de asignar formatos únicamente a las celdas que usarás y no a todas.

Gracias, voy a modificarlo. 

 

¿Cómo puedo obrar el formato fr las celdas XFD?

Hice lo que me indicasteis pero parece que no ha funcionado.

Para eliminar filas/columnas no usadas y que probablemente tienen formatos aplicados y están haciendo que el libro sea demasiado grande se sigue el siguiente procedimiento:

1. Vas a la última celda que REALMENTE estés usando.
2. Eliminas todas las columnas que estén a su derecha.
3. Otra vez te ubicas en la última celda que REALMENTE estes usando.
4. Eliminas todas las filas que estén debajo.
5. Repites los pasos del 1 al 4 para cada hoja
6. Guardas tu libro

Yo lo hice solo en una hoja de tu archivo (la que se llama 2I) y el archivo se redujo en 8MB (de 37 a 29)