domingo, abril 21, 2013

Remediar el problema de las celdas combinadas en Excel

Empiezo esta nota por declarar abiertamente y sin remordimientos: detesto la funcionalidad "Combinar y centrar" de Excel. Y no es una cuestión de gustos personales. Ya hemos expuesto parte, y sólo una pequeña parte, de los problemas que genera el uso de Combinar y Centrar (y también mostré las ventajas de usar "centrar en la selección" en el menú Formato de Celdas – Alineación).

Sin embargo y a pesar de ser una de las peores prácticas en Excel, cada tanto recibo archivos plagados de celdas combinadas. Por lo general se trata de archivos de colegas de trabajo que, después de haber invertido horas en crear la hoja, no entienden por qué se dan comportamientos extraños o imprevistos.

Últimamente tuve que rediseñar un cuaderno, con varias hojas, donde el uso de Combinar y Centrar generaba todo tipo de problemas. Dada la cantidad de correcciones a hacer, decidí programar una macro que haga la tarea: encontrar las celdas combinadas y "descombinarlas" (tal vez debería decir deshacer o descomponer).

El código es el siguiente

Sub unmerge_Cells()
    Dim rngCell As Range
   
    'comprobar numero de celdas en la seleccion
    On Error GoTo errOverFlow
    If Selection.Count = 1 Then
        MsgBox "Por favor, seleccionar un area de la hoja", vbExclamation
        Exit Sub
    End If
    If Selection.Count > 1000000 Then
        MsgBox "Demasiadas celdas en la seleccion", vbCritical
        Exit Sub
    End If
   
    'descombinar celdas combinadas
    For Each rngCell In Selection
        If rngCell.MergeCells = True Then
            rngCell.UnMerge
        End If
    Next rngCell
      
    Exit Sub
   
errOverFlow:
If Err.Number = 6 Then
MsgBox "Se han seleccionado demasiadas celdas", vbCritical
Exit Sub
End If
   
End Sub


El código revisa todas las celdas en el área seleccionada y deshace las combinaciones. Como puede verse, hemos limitado la cantidad de celdas que puede contener el área, para evitar problemas de "overflow".

El mejor lugar para guardar el código es en un módulo del libro Personal.xls(m), de manera de poder usarlo en todos los cuadernos abiertos.

Como decía más arriba, la alternativa a Combinar y Centrar es usar Formato de Celdas-Alineación-Centrar en la Selección. Este código, como el anterior, deshace las combinaciones de celdas y luego aplica el centrado en la selección, manteniendo de esta manera el diseño de la hoja (la excepción es cuando la combinación de celdas incluye más de una fila)

Sub unmerge_and_center()
    Dim rngCell As Range
    Dim rngMergedRange As Range
    Dim strMergeAreaAddress As String
   
    'comprobar numero de celdas en la seleccion
    On Error GoTo errOverFlow
    If Selection.Count = 1 Then
        MsgBox "Por favor, seleccionar un area de la hoja", vbExclamation
        Exit Sub
    End If
    If Selection.Count > 1000000 Then
        MsgBox "Demasiadas celdas en la seleccion", vbCritical
        Exit Sub
    End If
   
    'descombinar celdas combinadas
    For Each rngCell In Selection
        If rngCell.MergeCells = True Then
            Set rngMergedRange = rngCell.MergeArea
            strMergeAreaAddress = rngMergedRange.Address
            Range(strMergeAreaAddress).Select
            rngCell.UnMerge
            Range(strMergeAreaAddress).HorizontalAlignment = xlCenterAcrossSelection
        End If
    Next rngCell
   
    Exit Sub
   
errOverFlow:
If Err.Number = 6 Then
MsgBox "Se han seleccionado demasiadas celdas", vbCritical
Exit Sub
End If

End Sub


El archivo con los códigos se puede descargar aquí.

19 comentarios:

  1. Sin remordimientos: yo también lo detesto :-)

    Una pena que en varias filas no funcione. Ya que si hay celdas combinadas (A1:A4) no funciona.

    Muchas gracias.

    ResponderBorrar
  2. Funciona también si en celdas combinadas con más de una fila. Solo hay que tomar en cuenta que el "centrar en la selección" se refiere a la fila de la celda en cuestión. Otro detalle a tomar en cuenta es que en ciertos casos, para ver el contenido de la celda "descombinada" hay que ampliar el ancho de la columna.

    ResponderBorrar
  3. Miguel Raúl Spindiak22 abril, 2013 16:37

    Los problemas referidos, son principalmente generados por una falta de "buenas prácticas". Uno puede tener una hoja con los datos, lo más espartana posible, en la que están los datos crudos con sus títulos. Otra hoja con resultados especiales o una tabla dinámica, etc. Otra hoja para exponer (o imprimir) los datos, en donde si se quiere se puede combinar celdas o cualquier otra operación, que por más compleja que se haga, nunca afectará a la fuente de datos.
    Jorge: creo que amerita de tu parte, una(s) notas sobre como, si no se gobierna bien un libro de Excel, con todas las posibilidades que nos ofrece, se empiezan a ensuciar las tablas, se ponen elementos que después no se sabe porqué están, etc., cuando con un poco de prolijidad se obtienen excelentes resultados.

    ResponderBorrar
  4. Hola Jorge. Por 1ª vez no entendi lo que quisiste explicar. al menos en mi version de excel 2010, no aparece la funcion "centrar en la seleccion", tal como lo indicas al comienzo.
    Por otra parte, en los casos que quieroeliminar todas als combinaciones de celdas de un libro, simplemente selecciono todas las hojas, y en la hoja selecciono todas las celdas, y en alineacion, destildo "combinar" y resuelto el problema.
    abrazo.

    ResponderBorrar
  5. De verdad que la opción "Combinar" trae muchos inconvenientes. Gracias por aportar y publicar las soluciones. Saludos.

    ResponderBorrar
  6. Hola José,
    la opción "centrar en la selección" existe en todas las versiones de Excel, por lo menos desde XL97, incluida la versión 2013 (fijate en esta imagen).
    La idea de la nota es eliminar la combinación de celdas y reemplazarla por "centrar en la selección", en una única operación.
    Si se trata de eliminar solamente las combinaciones, basta con tu técnica.

    ResponderBorrar
  7. Hola Miguel,
    muy cierto tu comentario. En realidad se trata más de una cuestión de buenas prácticas que de prolijidad. Casi podríamos citar a Maquiavelo: "divide y reinarás". La división entre datos, cálculos y presentación "el primer mandamiento" en la tabla de las leyes de Excel.
    La segunda ley es "no usarás la combinación de celdas".
    No se si habrás visto la serie de tres notas sobre "Buenas y malas prácticas en Excel" en el blog (nota 1, nota 2, nota 3).

    ResponderBorrar
    Respuestas
    1. espectacular !!! gracias!!!, como siempre !!!

      Borrar
  8. Excelente aporte Jorge, lo tomo para mis archivos... gracias

    ResponderBorrar
  9. Gracias por expresar en voz alta lo que llevo tanto tiempo guardado dentro! Mis equipos no me entienden! Muy útil, gracias!

    ResponderBorrar
  10. Y macro SOLO para descubrir y seleccionar las combinadas?

    ResponderBorrar
  11. En los próximos días publicaré una nota sobre el tema.

    ResponderBorrar
  12. Hola,
    Tengo el siguiente casoen la columna A, twngo cendas combinadas desde la fila 1 hasta la fila 4.
    Necesito el codigo o funcion para rescatar el numero total de filas, ( en el ejemplo 4)

    ResponderBorrar
  13. Enrique, podrías usar una UDF (función definida por el usuario - Vba) como ésta

    Function count_merged_rows(cellSelected As Range)
    count_merged_rows = cellSelected.MergeArea.Rows.Count
    End Function

    donde cellSelected es la celda activa al seleccionar el área de celdas combinadas.

    ResponderBorrar
  14. Ante todo buenas tardes y muchas gracias por el aporte. La verdad es muy buena la página, especialmente para mi que se de programación con otro software (Stata), y estoy "metiéndome" con VBA.
    Te hago una consulta, me gustaría introducir en el código, una vez que quita la combinación, que rellene el valor de la celda concatenando con la celda de abajo. Es decir, supongamos que A1 y A2 era enero, A2 $ y B2 USD, me gustaría que en A1 me quede enero_$ y A enero_USD. Se hacerlo con STATA pero quiero aprenderlo con VBA, asi que si pudieras reconmendas alguna bibliografía o tutorial sobre la temática te agradecería mucho. Saludos! MI

    ResponderBorrar
  15. Hola, se puede hacer con facilidad pero la explicación excede el marco de un comentario. Te sugiero ver algunas de las muchas páginas Web de Vba o foros.

    ResponderBorrar
  16. Gracias por compartir la informacion

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.