Intercambiar rangos en Excel

En todo proceso de diseño de una hoja en Excel es prácticamente inevitable tener que reordenar rangos (filas, columna o celdas).
Por lo general nos basta con arrastrar el rango a la posición deseada. Pero en no pocos casos debemos intercambiar posiciones, poner un rango en lugar del otro.
La técnica más común es usar Copiar (o Cortar) y Pegar, usando una ubicación transitoria, o arrastrando una de las columnas (o filas o celdas) a un área "libre" de la hoja y moviendo luego los objetos a la posición deseada

Una forma más eficiente es seleccionar uno de los rangos y manteniendo apretada la tecla Mayúsculas (Shift), apuntar con el mouse al borde indicado y arrastrar el rango a la posición del rango a intercambiar

Lo mismo puede hacer con celdas o filas.

En ciertas situaciones estas técnicas pueden ser engorrosas, en particular cuando queremos realizar el intercambio entre celdas que se encuentran en distintas columnas. Por ejemplo, si tenemos una lista de turnos de trabajo, donde los operarios piden con cierta frecuencia cambiar su turno:

Con un poco de código Vba (macros) podemos crear una herramienta útil para intercambiar celdas con facilidad:

Sub InterCambiarCeldas()
    Dim rngCell2 As Range
    Dim cellTemp As String
   
    'verificar que se haya elegido solo una celda
    If Selection.Count > 1 Then
        MsgBox "Debe elegirse solo una celda", vbCritical
        Exit Sub
    End If
   
    'verificar que la celda contenga un valor
    If Len(ActiveCell) = 0 Then
        MsgBox "Celda vacia", vbExclamation
        Exit Sub
    End If
     
    On Error GoTo errCancel
   
    Set rngCell2 = Application.InputBox(prompt:="Elija la celda a intercambiar", _
                        Title:="Celda a intercambiar", Type:=8)
    If rngCell2.Count <> 1 Then
        MsgBox "Debe elegirse solo una celda", vbCritical
        Exit Sub
    End If
    If Len(rngCell2) = 0 Then
        MsgBox "La celda elegida esta vacia", vbExclamation
        Exit Sub
    End If

   
    cellTemp = ActiveCell.Value
   
    ActiveCell.Value = rngCell2.Value
    rngCell2 = cellTemp
   
    Exit Sub
   
errCancel:
If Err.Number = 424 Then
MsgBox "Operacion cancelada", vbExclamation
Exit Sub
End If


End Sub

El código va en un módulo común del editor de Vba, y para que podamos usarlo en todo cuaderno abierto lo guardamos en un módulo del cuaderno Personal.xls(m). Par apoder usar la macro con facilidad le asignamos un atajo de teclado

o un ícono en la barra de acceso rápido

Con este código todo lo que tenemos que hacer es seleccionar la primera celda a intercambiar, apretar Ctrl-Mayúsculas-Enter para accionar la macro, elegir la segunda celda y apretar Ok. Si elegimos más de una celda o una celda vacía, aparece un aviso y el código termina; lo mismo si el usuario aprieta el botón Cancelar del al forma

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í.

Listas desplegables en Excel – La guía de JLD

Las listas desplegables son una de las herramientas indispensables en nuestros dashboards (tableros de comandos), informes y/o gráficos dinámicos y toda plantilla Excel donde se requiera manejar la interacción del usuario con el modelo.

Después de varios meses de preparación y correcciones (gracias a los correctores voluntarios por sus aportes, en particular a Javi) pongo a disposición de mis lectores la primer guía de la serie "Caja de herramientas Excel": Listas Desplegables.

Esta guía, dirigida tanto al usuario experimentado como al principiante, expone de forma didáctica y sistemática las distintas técnicas con las cuales podemos crear listas desplegables en Excel, desde el uso sencillo de Validación de Datos hasta técnicas con Vba (Visual Basic for Applications).

Cada capítulo va acompañado de ejemplos que pueden descargarse (sin costo adicional).

La guía tiene un costo de 7.99 Euros y puede descargarse aquí.

Actualización: si estás interesado en recibir la guía contactame por mail privado (ver el enlace Ayuda en la barra superior del blog).

Guías JLD, Listas desplegables – Muchas gracias

La respuesta a mi pedido de colaboradores para la corrección del borrador de la lista ha superado todas mis expectativas.

Muchas gracias a todos los que se han ofrecido a ayudar.

Dada la cantidad de voluntarios he decidido cambiar de política. En lugar de enviar el borrador a los primeros cinco mails recibidos, haré un sorteo para seleccionar los cinco colaboradores.

Por supuesto, usaré Excel para el sorteo con el modelo que muestro en esta nota.

En las próximas horas estaré enviando el borrador a los voluntarios elegidos.

Nuevamente, muchas gracias a todos.

17/04/2013 - La versión final está disponible.

Guías JLD – Listas desplegables, primer borrador

17/04/2013 - La versión final está disponible.

Después de un largo proceso de preparación, acabo de completar el primer borrador de la primer guía de la serie "Cajas de Herramientas Excel JLD".

Esta primer guía desarrolla en forma sistemática y didáctica las técnicas para crear listas desplegables en Excel.

Los temas tratados van desde la creación de listas desplegables sencillas con validación de datos hasta el uso de Vba (macros) para la creación de listas desplegables dependientes con autocompletado de valores. Cada tema va acompañado por archivos con los ejemplos.

El índice de los temas tratados

Como en el pasado, recurro a mis lectores en búsqueda de editores/correctores. Los primeros cinco lectores que me envíen su dirección de mail (por favor, no ponerla en un comentario; enviarlo a la dirección que aparece en el enlace Ayuda), recibirán como recompensa la versión final en forma gratuita.

Desde ya muchas gracias.