Operaciones básicas con VBA relacionadas con celdas y rangos

Sergio 07/04/2021
VBA operaciones básicas con celdas y rangos

Con VBA puedes realizar las mismas operaciones que si las haces de forma manual en la hoja. En este tutorial aprenderás a realizar algunas operaciones básicas con VBA como seleccionar, copiar, mover o editar celdas y rangos.

Hace un tiempo te mostré 41 formas de hacer referencia a celdas mediante VBA. Hoy repaso las más utilizadas y lo complemento con algunas acciones más.

Seleccionar una celda o un rango con VBA

Antes de nada, métete esto en la cabeza: para trabajar con celdas y rangos en VBA NO es necesario seleccionarlos. De hecho, en la mayoría de los casos, es mejor no hacerlo (lo abordaré más adelante).

Sin embargo es importante que entiendas cómo funciona la selección de celdas. Es algo imprescindible para aprender VBA y muchos de los conceptos que trataré en siguientes tutoriales.

Seleccionar una celda o rango con VBA

Para seleccionar una sola celda (por ejemplo A1) puedes usar el siguiente código:

Sub SeleccionarCelda()
    Range("A1").Select
End Sub

En el procedimiento anterior, la primera y última líneas abren y cierran el código y la línea central es la que realiza el trabajo.

  • Range("A1") le indica a VBA la dirección de la celda con la que vas a trabajar.
  • Select es un método del objeto Range que selecciona la celda o el rango especificado en dicho objeto. Las referencias de las celdas deben ir entre comillas dobles (las del 2).

Debes tener en cuenta que en el procedimiento anterior estás seleccionando la celda A1 de la hoja activa, por ello no necesitas hacer más especificaciones. En caso de que la celda que deseas seleccionar se encuentre en otra hoja (aunque también podría estar en la misma), necesitas activar primero la hoja y, a continuación, seleccionar la celda.

Sub SeleccionarCelda()
    Worksheets("Hoja1").Activate
    Range("A1").Select
End Sub

Del mismo modo, también puedes activar un libro de Excel diferente, luego activar una hoja y, a continuación especificar una celda.

Sub SeleccionarCelda()
    Workbooks("Libro2.xlsx").Worksheets("Hoja2").Activate
    Range("A1").Select
End Sub

Ten en cuenta que cuando hagas referencia a un libro, tienes que utilizar el nombre completo junto con la extensión del archivo (.xlsx en el ejemplo anterior). En caso de que el libro de trabajo no se haya guardado por primera vez, no es necesario utilizar la extensión.

Como puedes comprobar, los ejemplos anteriores no son muy útiles, pero más adelante utilizaré estos mismos conceptos para copiar y pegar celdas con VBA.

De la misma forma que seleccionas una celda, puedes hacerlo con un rango (tanto fijo como variable). En un rango fijo, las referencias siempre son las mismas. En un rango de tamaño variable, no es posible conocer el tamaño de antemano y necesitas usar un poco de magia VBA.

Seleccionar un rango de tamaño fijo

Te muestro un par de códigos para seleccionar un rango fijo:

Sub SeleccionarRango()
    Range("A1:D20").Select
End Sub

Sub SeleccionarRango()
    Range("A1", "D20").Select
End Sub

En el primero de ellos se hace referencia al rango completo. En el segundo, se toma la dirección de la celda superior izquierda del rango (A1) y la dirección de la celda inferior derecha (D20) para seleccionar las celdas comprendidas entre ellas.

Si la selección de celdas debe hacerse en un libro diferente o en una hoja diferente, necesitas decirle a VBA los nombres exactos de esos objetos. Por ejemplo, el siguiente código selecciona  el rango A1:D20 de la Hoja2 del Libro2:

Sub SeleccionarRango()
    Workbooks("Libro2.xlsx").Worksheets("Hoja2").Activate
    Range("A1:D20").Select
End Sub

En los ejemplos anteriores ya se sabía de antemano el número de celdas a seleccionar. ¿Qué ocurre entonces cuando desconoces el número de filas que tiene el rango?¿Y si quieres seleccionar todas las celdas con contenido?

En estos casos es necesario utilizar algunos métodos…

Seleccionar un rango de tamaño variable

Existen varias formas de seleccionar un rango de celdas. El método que selecciones dependerá de cómo estén estructurados los datos o de lo cómodo/a que te sientas utilizándolo.

Seleccionar un rango con la propiedad CurrentRange

En los casos en los que quieres seleccionar un rango contiguo de celdas pero no sabes cuántas filas o columnas contiene, puedes utilizar la propiedad CurrentRange del objeto Range.

Esta propiedad abarca todas las celdas contiguas de un rango de datos a partir de una celda. Dicho con otras palabras, si partes de una celda, por ejemplo A1, la selección que proporciona CurrentRange es la de todas las celdas que no se encuentren vacías alrededor de A1.

Te pongo un ejemplo. El siguiente código selecciona la ‘región actual’ alrededor de la celda A1:

Sub SeleccionarRegionActual()
    Range("A1").CurrentRegion.Select
End Sub

Como he comentado, este código es bueno cuando los datos no contienen filas o columnas en blanco.

Propiedad CurrentRegion Excel

En caso de que el rango contenga filas o columnas en blanco, los datos ubicados por debajo o hacia la derecha no se seleccionarán. En la siguiente imagen se muestra que el código anterior selecciona el rango hasta la fila 5.

Propiedad CurrentRegion VBA

¿Quieres comprobarlo? Escribe algo en la celda A6 y vuelve a ejecutar el código. Ahora se seleccionarán todas las celdas.

Seleccionar un rango con la propiedad UsedRange

UsedRange te permite hacer referencia a cualquier celda que contenga datos. Por ejemplo, el siguiente código selecciona todas las celdas usadas en la hoja activa.

Sub SeleccionarRangoUsado()
    ActiveSheet.UsedRange.Select
End Sub

Ten en cuenta que en el caso de que la última celda con datos se encuentre muy alejada del resto de datos de la hoja, este código seleccionaría todas las celdas hasta esta última.

Seleccionar un rango con la propiedad End

En mi opinión, este es el método más útil para seleccionar, mediante VBA, rangos de tamaño desconocido.

La propiedad End te permite seleccionar la última celda con un valor. Esto te permite simular el efecto de las teclas Ctrl + Flecha.

Te pongo un ejemplo. Imagina que tienes un conjunto de datos como el que se muestra en la siguiente imagen y quieres seleccionar rápidamente la última celda de la columna A.

Propiedad End VBA Excel

El problema aquí es que los datos pueden cambiar y no es posible conocer cuántas celdas contienen datos. Si usaras el teclado podrías seleccionar la celda A1 y luego presionar las teclas Ctrl + Flecha abajo.

Este código VBA tendría el mismo efecto:

Sub IrAUltimaCelda()
    Range("A1").End(xlDown).Select
End Sub

El código anterior seleccionaría únicamente la última celda de la columna A.

Del mismo modo, puedes usar End(xlRight) para saltar, en la misma fila, a la última columna con datos.

Sub IrAUltimaCelda()
    Range("A1").End(Right).Select
End Sub

Estos dos códigos seleccionan únicamente la última celda de la columna o de la fila pero, ¿cómo se seleccionan todas las celdas con datos hasta la última. Echa un vistazo al siguiente código:

Sub SeleccionarCeldasDatos()
    Range("A1", Range("A1").End(xlDown)).Select
End Sub

En este código he utilizado la primera y la ultima referencia de la celda que necesito seleccionar. La primera parte del rango ("A1") es la parte fija y la segunda (Range("A1").End(xlDown)), la parte variable. De esta forma siempre me aseguro de que todas las celdas quedan seleccionadas.

Diferencia entre CurrentRegion y End

Si te estás preguntando por qué usar la propiedad End para seleccionar el rango relleno en lugar de usar CurrentRegion, déjame explicarte algo: Con End puedes especificar la celda de inicio. Por ejemplo si tienes datos en el rango A1:D20 pero la primera fila contiene encabezados, puedes usar la propiedad End a partir de la segunda fila para seleccionar exclusivamente los datos:

Sub SeleccionarCeldasDatos()
    Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select
End Sub

Con CurrentRegion esto no es posible, pues se seleccionaría todo el conjunto de datos incluidos los encabezados.

Copiar celdas o rangos con VBA

Como mencioné anteriormente, no es necesario seleccionar una celda para realizar acciones sobre ella. A continuación te mostraré cómo copiar celdas y rangos sin seleccionarlos.

Copiar una celda

Para copiar la celda A1 y pegarla, por ejemplo en D1, puedes utilizar el siguiente procedimiento:

Sub CopiarCelda()
 Range("A1").Copy Range("D1")
End Sub

Observa que el método Copy del objeto Range copia la celda de la misma forma que si usaras Ctrl + C y la pega en el lugar especificado.

En el ejemplo anterior, la celda de destino se encuentra en la misma línea donde se utiliza el método Copy. Aunque la línea se entiende bien, puedes hacerla aún más legible usando el siguiente procedimiento:

Sub CopiarCelda()
    Range("A1").Copy Destination:=Range("D1")
End Sub

Estos dos códigos copian y pegan tanto los valores como los formatos.

¿Te has fijado en que no he usado el método Select? Da igual en qué parte del a hoja de cálculo te encuentres. Estos códigos copiarán la celda A1 y la pegarán en D1.

Ten en cuenta que el código anterior sobrescribirá cualquier contenido que exista en la celda D1. Si quieres que Excel te avise si esta celda ya tiene contenido sin sobrescribirlo, puedes usar el siguiente código:

Sub CopiarCelda()
    If Range("D1") <> "" Then
        Response = MsgBox("¿Quieres sobrescribir los datos existentes?", vbYesNo)
    End If
    If Response = vbYes Then
        Range("A1").Copy Range("D1")
    End If
End Sub

Copiar un rango de celdas fijo

Para copiar el rango A1:D20 en J1:M20 puedes utilizar el siguiente procedimiento:

Sub CopiarRango()
    Range("A1:D20").Copy Range("J1")
End Sub

Observa que solo he especificado una celda de destino. El código copiará automáticamente el rango a partir de la celda J1.

De la misma forma puedes usar esta construcción para copiar datos de una celda a otra. El siguiente código copiará A1:D20 de la hoja activa, en la Hoja2.

Sub CopiarRango()
    Range("A1:D20").Copy Worksheets("Hoja2").Range("A1")
End Sub

Este código copia los datos de la hoja activa. Por ello, antes de ejecutar la macro asegúrate de que la hoja que contiene los datos se encuentra activa en ese momento. Si quieres asegurarte de que realmente los datos copiados son los que tienen que copiarse, usa este código para especificar tanto la hoja de origen como la de destino:

Sub CopiarRango()
    Worksheets("Hoja1").Range("A1:D20").Copy Worksheets("Hoja2").Range("A1")
End Sub

Lo bueno del código anterior es que no importa qué hoja esté activa. Siempre se copiará el rango de datos de la Hoja1 en la Hoja2.

También puedes copiar un rango con nombre usando su nombre en lugar de la referencia. Por ejemplo, si tienes un rango llamado ‘Ventas’, puedes usar el siguiente código para copiar essos datos a la Hoja2.

Sub CopiarRango()
    Range("Ventas").Copy Worksheets("Hoja2").Range("A1")
End Sub

Si has establecido el ámbito del nombre como “libro”, no necesitas estar en la hoja que tiene el nombre para ejecutar el código. Si tiene el ámbito “hoja”, necesitas activar previamente la hoja.

Copiar un rango de celdas variable

Una forma de copiar rangos de tamaño variable es convertirlos en rangos con nombre o en tablas y utilizar los procedimientos anteriores.

Pero si no es posible hacer esto, puedes utilizar la propiedad CurrentRegion o la propiedad End.

El siguiente código copiará la región actual de la hoja activa y la pegará en la Hoja2.

Sub CopiarRegionActual()
    Range("A1").CurrentRegion.Copy Worksheets("Hoja2").Range("A1")
End Sub

Para copiar la primera columna de tu conjunto de datos, desde A1 hasta la última celda con datos y pegarla en la Hoja2, usa este procedimiento:

Sub CopiarRegionActual()
    Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Hoja2").Range("A1")
End Sub

Insisto nuevamente en que para trabajar con rangos de celdas no es necesario seleccionarlas.

Asignar rangos a variables de objeto

En todos los ejemplos anteriores he utilizado la ubicación de la celda (por ejemplo Workbooks("Libro2.xlsx").Worksheets("Hoja2").Range("A1"))

Para hacer el código más manejable, puedes asignar estos rangos a variables de objeto para luego usar esas variables en el código.

Por ejemplo, en el siguiente código he asignado los rangos de origen y de destino a variables de objeto y luego he utilizado estas variables para copiar los datos de un rango al otro:

Sub CopiarRango()
    Dim Origen As Range
    Dim Destino As Range
    Set Origen = Worksheets("Hoja1").Range("A1:D20")
    Set Destino = Worksheets("Hoja2").Range("A1")
    Origen.Copy Destino
End Sub

En el procedimiento anterior, con las instrucciones Dim se comienza declarando las variables como objetos Range. A continuación, se asigna el rango a estas variables usando la instrucción Set. Para finalizar simplemente se usan las variables para realizar la copia.

Cómo introducir datos en la siguiente celda vacía (con InputBox)

Una de las operaciones básicas con VBA que se realiza con más frecuencia es la de introducir datos en la siguiente celda vacía a modo de tabla base de datos.

Mi técnica favorita de ‘captura’ de datos es mediante la función InputBox con la cual se muestra un cuadro al usuario y este debe introducir un valor en un cuadro de texto. Para esto usa el siguiente procedimiento:

Sub IntroducirDatos()
    Dim MiRango As Range
    Set MiRango = Range("A1").End(xlDown).Offset(1, 0)
    Set Producto = MiRango.Offset(0, 1)
    Set Cantidad = MiRango.Offset(0, 2)
    Set Importe = MiRango.Offset(0, 3)
    MiRango.Value = MiRango.Offset(-1, 0).Value + 1
    Producto.Value = InputBox("Producto")
    Cantidad.Value = InputBox("Cantidad")
    Importe.Value = InputBox("Importe")
End Sub

El código anterior usa la función InputBox para obtener los datos del usuario y luego introducirlos en las celdas especificadas.

Observa que no he utilizado en ningún momento referencias a celdas fijas, sino las propiedades End y Offset para encontrar la última celda vacía e introducir los datos en ella.

Aunque este procedimiento está listo para usarse, no recomiendo usarlo aún, pues faltan detalles por pulir. Por ejemplo, si introduces una cadena de texto cuando el cuadro de diálogo solicita la cantidad o el importe (valores numéricos), verás que Excel lo permite. Para mejorarlo puedes utilizar una instrucción If para comprobar si el valor es numérico o no y permitir su introducción en consecuencia.

Bucles en celdas o rangos

Otra tarea muy habitual al trabajar con VBA recorrer un conjunto de celdas, filas o columnas un número determinado de veces. Es lo que se conoce como bucle. Esto puede ser útil cuando quieres analizar todas y cada una de las celdas de un rango para realizar una tarea con ella.

Por ejemplo, si quieres resaltar una de cada dos filas en el rango seleccionado, necesitarás hacer un bucle para que compruebe el número de fila. Del mismo modo, si quieres resaltar todas las celdas que contienen números negativos para cambiar el color de la fuente a rojo, necesitas crear un bucle que recorra las celdas y haga las modificaciones solo en aquellas que cumplan la condición.

El siguiente código hace un bucle en las filas de las celdas seleccionadas y resalta las filas pares:

Sub ResaltaFilasAlternas()
    Dim MiRango As Range
    Dim MiFila As Range
    Set MiRango = Selection

    For Each MiFila In MiRango.Rows
        If MiFila.Row Mod 2 = 0 Then
            MiFila.Interior.Color = vbGreen
        End If
    Next MiFila
End Sub

El procedimiento anterior utiliza la función Mod para comprobar si el número de fila en la selección es par o impar. Si el número de fila es par, se resalta en color verde.

A continuación te muestro otro procedimiento en el que el código pasa por todas las celdas y resalta las que tienen un valor negativo:

Sub ResaltaNegativos()
    Dim MiRango As Range
    Dim MiCelda As Range
    Set MiRango = Selection

    For Each MiCelda In MiRango
        If MiCelda < 0 Then
            MiCelda.Interior.Color = vbRed
        End If
    Next MiCelda
End Sub

¿Y dónde introduces todo este código?

Si te estás haciendo esta pregunta, échale un vistazo a este tutorial donde te muestro dónde debes introducir el código de los procedimientos en función de la naturaleza y del objetivo.

Resumen

Si estás comenzando a usar VBA, los códigos que te he propuesto en el artículo son ideales para entender cómo funcionan algunas propiedades de selección y copiado de datos. Para sacarle más partido a estos procedimientos profundiza en los conceptos de “Rango” (entendiéndolo como objeto VBA) y en cómo se aplican las propiedades a un determinado objeto.

Categorías: Macros y VBA