Trabajar con celdas y rangos en Excel VBA (seleccionar, Copiar, Mover, editar)

enero 2, 2021 admin 0 Comments

Cuando trabaja con Excel, la mayor parte de su tiempo se dedica al área de la hoja de trabajo: tratar con celdas y rangos.

y si desea automatizar su trabajo en Excel usando VBA, necesita saber cómo trabajar con celdas y rangos usando VBA.

Hay muchas cosas diferentes que puedes hacer con rangos en VBA (como seleccionar, Copiar, mover, editar, etc.).

para cubrir este tema, dividiré este tutorial en secciones y le mostraré cómo trabajar con celdas y rangos en Excel VBA usando ejemplos.,

comencemos.

todos los códigos que menciono en este tutorial deben colocarse en el Editor VB. Vaya a la sección’ dónde poner el código VBA ‘ para saber cómo funciona.

si estás interesado en aprender VBA de la manera más fácil, echa un vistazo a mi formación en línea de Excel VBA.

este Tutorial cubre:

seleccionar una celda / rango en Excel usando VBA

para trabajar con celdas y rangos en Excel usando VBA, no necesita seleccionarlo.

en la mayoría de los casos, es mejor no seleccionar celdas o rangos (como veremos).,

a pesar de eso, es importante que revises esta sección y entiendas cómo funciona. Esto será crucial en su aprendizaje de VBA y se utilizarán muchos de los conceptos cubiertos aquí a lo largo de este tutorial.

así que vamos a empezar con un ejemplo muy simple.

seleccionar una sola celda usando VBA

Si desea seleccionar una sola celda en la hoja activa (digamos A1), puede usar el siguiente código:

Sub SelectCell()Range("A1").SelectEnd Sub

el código anterior tiene la parte obligatoria ‘Sub’ y ‘End Sub’, y una línea de código que selecciona la celda A1.,

rango («A1») le dice a VBA la dirección de la celda a la que queremos referirnos.

Select es un método del objeto rango y selecciona las celdas / rango especificado en el objeto rango. Las referencias de celda deben estar entre comillas dobles.

este código mostrará un error en caso de que una hoja de gráfico sea una hoja activa. Una hoja de gráficos contiene gráficos y no se usa ampliamente. Dado que no tiene celdas/rangos, el código anterior no puede seleccionarlo y terminaría mostrando un error.

tenga en cuenta que, dado que desea seleccionar la celda en la hoja activa, solo necesita especificar la dirección de la celda.,

pero si desea seleccionar la celda en otra hoja (digamos Sheet2), primero debe activar Sheet2 y luego seleccionar la celda en ella.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

del mismo modo, también puede activar un libro de trabajo, luego activar una hoja de trabajo específica en él y luego seleccionar una celda.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

tenga en cuenta que cuando se refiere a libros de trabajo, debe usar el nombre completo junto con la extensión de archivo (.xlsx en el código anterior). En caso de que el libro de trabajo nunca se haya guardado, no necesita usar la extensión de archivo.,

ahora, estos ejemplos no son muy útiles, pero verá más adelante en este tutorial cómo podemos usar los mismos conceptos para copiar y pegar celdas en Excel (usando VBA).

así como seleccionamos una celda, también podemos seleccionar un rango.

en el caso de un rango, podría ser un rango de tamaño fijo o un rango de tamaño variable.

en un rango de tamaño fijo, sabrá qué tan grande es el rango y puede usar el tamaño exacto en su código VBA. Pero con un rango de tamaño variable, no tiene idea de cuán grande es el rango y necesita usar un poco de magia VBA.

veamos cómo hacer esto.,

Seleccionar un rango de tamaño fijo

Aquí está el código que seleccionará el rango A1: D20.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

otra forma de hacer esto es utilizando el siguiente código:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

el código anterior toma la dirección de celda superior izquierda (A1) y la dirección de celda inferior derecha (D20) y Selecciona todo el rango. Esta técnica se vuelve útil cuando está trabajando con rangos de tamaño variable (como veremos cuando la propiedad End se cubra más adelante en este tutorial).,

si desea que la selección ocurra en un libro de trabajo diferente o en una hoja de trabajo diferente, debe decirle a VBA los nombres exactos de estos objetos.

por ejemplo, el siguiente código seleccionaría el rango A1: D20 en la hoja de trabajo Sheet2 en el libro de trabajo Book2.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

ahora, qué pasa si no sabes cuántas filas hay. Qué pasa si desea seleccionar todas las celdas que tienen un valor en él.

en estos casos, debe usar los métodos que se muestran en la siguiente sección (Al seleccionar un rango de tamaño variable).,

Seleccionar un rango de tamaño variable

hay diferentes maneras de seleccionar un rango de celdas. El método que elija dependerá de cómo se estructuran los datos.

en esta sección, cubriré algunas técnicas útiles que son realmente útiles cuando trabajas con rangos en VBA.

seleccionar usando la propiedad CurrentRange

en los casos en los que no sepa cuántas filas/columnas tienen los datos, puede usar la propiedad CurrentRange del objeto Range.

la propiedad CurrentRange cubre todas las celdas rellenas contiguas en un rango de datos.,

a continuación se muestra el código que seleccionará la región actual que contiene la celda A1.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

el método anterior es bueno cuando tiene todos los datos como una tabla sin filas / columnas en blanco.

pero en caso de que tenga filas/columnas en blanco en sus datos, no seleccionará las que estén después de las filas / columnas en blanco. En la imagen de abajo, el código CurrentRegion selecciona Datos hasta la fila 10 ya que la fila 11 está en blanco.

en tales casos, es posible que desee utilizar la propiedad UsedRange del objeto de la hoja de trabajo.,

seleccionar usando la propiedad UsedRange

UsedRange le permite hacer referencia a cualquier celda que se haya cambiado.

Por lo que el siguiente código seleccionaría todas las celdas utilizadas en la hoja activa.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

tenga en cuenta que en caso de que tenga una celda lejana que se haya utilizado, será considerada por el código anterior y todas las celdas hasta que se seleccione esa celda utilizada.

seleccione usando la propiedad End

ahora, esta parte es realmente útil.

la propiedad End le permite seleccionar la última celda rellena., Esto le permite imitar el efecto de Control abajo / arriba tecla de flecha o Control derecha / izquierda teclas.

intentemos entender esto usando un ejemplo.

supongamos que tiene un conjunto de datos como se muestra a continuación y desea seleccionar rápidamente las últimas celdas rellenas en la columna A.

el problema aquí es que los datos pueden cambiar y no sabe cuántas celdas se llenan. Si tiene que hacer esto usando el teclado, puede seleccionar la celda A1 y luego usar Control + Flecha abajo tecla, y seleccionará la última celda llena en la columna.

ahora veamos cómo hacer esto usando VBA., Esta técnica es útil cuando desea saltar rápidamente a la última celda llena en una columna de tamaño variable

Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub

el código anterior saltaría a la última celda llena en la columna A.

del mismo modo, puede usar el extremo(xlToRight) para saltar a la última celda llena en una fila.

Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub

ahora, qué pasa si desea seleccionar la columna completa en lugar de saltar a la última celda llena.,

Puede hacerlo utilizando el siguiente código:

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

en el código anterior, hemos utilizado la primera y la última referencia de la celda que necesitamos seleccionar. No importa cuántas celdas llenas haya, el código anterior seleccionará todas.

recuerde el ejemplo anterior donde seleccionamos el rango A1: D20 usando la siguiente línea de código:

rango («A1», «D20»)

Aquí A1 era la celda superior izquierda y D20 era la celda inferior derecha en el rango. Podemos utilizar la misma lógica en la selección de rangos de tamaño variable., Pero como no sabemos la dirección exacta de la celda inferior derecha, usamos la propiedad End para obtenerla.

En rango («A1», rango («A1»).End (xlDown)), «A1» se refiere a la primera celda y rango(«A1»).End (xlDown) se refiere a la última celda. Dado que hemos proporcionado ambas referencias, el método Select selecciona todas las celdas entre estas dos referencias.

del mismo modo, también puede seleccionar un conjunto de datos completo que tenga varias filas y columnas.

el siguiente código seleccionaría todas las filas/columnas rellenas a partir de la celda A1.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

En el código anterior, hemos utilizado Range(«A1»).Fin (xlDown).End (xlToRight) para obtener la referencia de la celda rellena inferior derecha del conjunto de datos.

diferencia entre usar CurrentRegion y End

Si te estás preguntando por qué usar la propiedad End para seleccionar el rango rellenado cuando tenemos la propiedad CurrentRegion, déjame decirte la diferencia.

con la propiedad End, puede especificar la celda de inicio., Por ejemplo, si tiene sus datos en A1: D20, pero la primera fila son encabezados, puede usar la propiedad End para seleccionar los datos sin los encabezados (utilizando el código a continuación).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

Pero el CurrentRegion automáticamente seleccione el conjunto de datos completo, incluyendo las cabeceras.

hasta ahora en este tutorial, hemos visto cómo hacer referencia a un rango de celdas usando diferentes maneras.

ahora veamos algunas formas en las que podemos usar estas técnicas para hacer algo de trabajo.,

Copie celdas / rangos usando VBA

Como mencioné al principio de este tutorial, no es necesario seleccionar una celda para realizar acciones en ella. Verá en esta sección Cómo copiar celdas y rangos sin siquiera seleccionarlos.

comencemos con un ejemplo simple.

copiando una sola celda

Si desea copiar la celda A1 y pegarla en la celda D1, el siguiente código lo haría.

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

tenga en cuenta que el método de copia del objeto de rango copia la celda (al igual que Control +C) y la pega en el destino especificado.,

en el código de ejemplo anterior, el destino se especifica en la misma línea donde se utiliza el método de copia. Si desea que su código sea aún más legible, puede usar el siguiente código:

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

Los códigos anteriores copiarán y pegarán el valor, así como el formato/fórmulas en él.

como ya habrás notado, el código anterior copia la celda sin seleccionarla. No importa dónde se encuentre en la hoja de trabajo, el código copiará la celda A1 y la pegará en D1.

además, tenga en cuenta que el código anterior sobrescribiría cualquier código existente en la celda D2., Si desea que Excel le informe si ya hay algo en la celda D1 sin sobrescribirlo, puede usar el código a continuación.

copiando un rango de tamaño fijo

Si desea copiar A1: D20 en J1: M20, puede usar el siguiente código:

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

en la celda de destino, solo necesita especificar la dirección de la celda superior izquierda. El código copiaría automáticamente el rango copiado exacto en el destino.

Puede usar la misma construcción para copiar datos de una hoja a otra.

el siguiente código copiaría A1: D20 de la hoja activa a Sheet2.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

La anterior copia los datos de la hoja activa. Así que asegúrese de que la hoja que tiene los datos es la hoja activa antes de ejecutar el código. Para estar seguro, también puede especificar el nombre de la hoja de trabajo mientras copia los datos.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

lo bueno del código anterior es que no importa qué hoja esté activa, siempre copiará los datos de Sheet1 y los pegará en Sheet2.

también puede copiar un rango con nombre usando su nombre en lugar de la referencia.,

por ejemplo, si tiene un rango con nombre llamado ‘SalesData’, puede usar el siguiente código para copiar estos datos a Sheet2.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

si el alcance del rango con nombre es todo el libro de trabajo, no necesita estar en la hoja que tiene el rango con nombre para ejecutar este código. Dado que el rango con nombre está delimitado para el libro de trabajo, puede acceder a él desde cualquier hoja utilizando este código.

Si tiene una tabla con el nombre Table1, puede usar el siguiente código para copiarla en Sheet2.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

también puede copiar un rango a otro Libro.,

en el siguiente ejemplo, copio la tabla de Excel (Table1), en el libro de Book2.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

Este código funcionaría solo si el Libro de trabajo ya está abierto.

copiar un rango de tamaño Variable

Una forma de copiar rangos de tamaño variable es convertirlos en rangos con nombre o tabla de Excel y usar los códigos como se muestra en la sección anterior.

pero si no puede hacer eso, puede usar la propiedad CurrentRegion o la propiedad End del objeto range.

el siguiente código copiaría la región actual en la hoja activa y la pegaría en Sheet2.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

Si desea copiar la primera columna de su conjunto de datos hasta la última celda llena y pegarla en Sheet2, puede usar el siguiente código:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

Si desea copiar las filas y las columnas, puede usar el siguiente código:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

tenga en cuenta que todos estos códigos no seleccionan las celdas mientras se ejecutan. En general, encontrará solo un puñado de casos en los que realmente necesita seleccionar una celda/rango antes de trabajar en él.,

asignar rangos a Variables de objeto

hasta ahora, hemos estado utilizando la dirección completa de las celdas (como libros de trabajo(«Book2.xlsx»).Hojas De Trabajo («Sheet1»).Range («A1»)).

para que su código sea más manejable, puede asignar estos rangos a variables de objeto y luego usar esas variables.

por ejemplo, en el siguiente código, he asignado el rango de origen y destino a variables de objeto y luego he utilizado estas variables para copiar datos de un rango a otro.

comenzamos declarando las variables como objetos de rango., Luego asignamos el rango a estas variables usando la instrucción Set. Una vez que el rango se ha asignado a la variable, simplemente puede usar la variable.

ingrese datos en la siguiente celda vacía (Usando el cuadro de entrada)

puede usar los cuadros de entrada para permitir que el Usuario ingrese los datos.

por ejemplo, supongamos que tiene el conjunto de datos a continuación y desea ingresar el registro de ventas, puede usar el cuadro de entrada en VBA. Usando un código, podemos asegurarnos de que llena los datos en la siguiente fila en blanco.,

el código anterior utiliza el cuadro de entrada de VBA para obtener las entradas del usuario y luego ingresa las entradas en las celdas especificadas.

tenga en cuenta que no utilizamos referencias de celda exactas. En su lugar, hemos utilizado la propiedad End y Offset para encontrar la última celda vacía y completar los datos en ella.

Este código está lejos de ser utilizable. Por ejemplo, si ingresa una cadena de texto cuando el cuadro de entrada solicita cantidad o cantidad, notará que Excel lo permite. Puede usar una condición If para verificar si el valor es numérico o no y luego permitirlo en consecuencia.,

bucle a través de celdas / rangos

hasta ahora podemos haber visto cómo seleccionar, copiar e ingresar los datos en celdas y rangos.

en esta sección, veremos cómo recorrer un conjunto de celdas/filas/columnas en un rango. Esto podría ser útil cuando desea analizar cada celda y realizar alguna acción basada en ella.

por ejemplo, si desea resaltar cada tercera fila en la selección, debe recorrer y verificar el número de fila., Del mismo modo, si desea resaltar todas las celdas negativas cambiando el color de fuente a rojo, debe recorrer y analizar el valor de cada celda.

Aquí está el código que recorrerá las filas en las celdas seleccionadas y resaltará las filas alternativas.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub

el código anterior utiliza la función MOD para verificar el número de fila en la selección. Si el número de fila es par, se resalta en color cian.

Aquí hay otro ejemplo donde el código pasa por cada celda y resalta las celdas que tienen un valor negativo en ella.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

tenga en cuenta que puede hacer lo mismo utilizando Formato condicional (que es dinámico y una mejor manera de hacer esto). Este ejemplo es solo con el propósito de mostrarle cómo funciona el bucle con celdas y rangos en VBA.

¿dónde poner el código VBA

preguntándose dónde va el código VBA en su libro de Excel?

Excel tiene un motor VBA llamado editor VBA. Debe copiar y pegar el código en la ventana de código del módulo del Editor VB.

Estos son los pasos para hacer esto:

ir a la pestaña Desarrollador.,
haga clic en la opción Visual Basic. Esto abrirá el editor VB en el backend.
En el panel Explorador de proyectos en el Editor VB, haga clic con el botón derecho en cualquier objeto del libro de trabajo en el que desee insertar el código. Si no ve el Explorador de proyectos, vaya a la pestaña Ver y haga clic en Explorador de proyectos.
vaya a insertar y haga clic en Módulo. Esto insertará un objeto de módulo para su libro de trabajo.
copie y pegue el código en la ventana del módulo.,

También puede que le gusten los siguientes tutoriales de Excel:

Trabajar con hojas de trabajo usando VBA.
Trabajar con libros de trabajo usando VBA.
Crear funciones definidas por el usuario en Excel.
Para el siguiente bucle en Excel VBA-una guía para principiantes con ejemplos.
Cómo Usar la función InStr de Excel VBA (con ejemplos prácticos).
Excel VBA Msgbox.
Cómo grabar una Macro en Excel.
Cómo ejecutar una Macro en Excel.
Cómo Crear un Complemento en Excel.,
Excel personal Macro Workbook / Save & utilice Macros en todos los libros de trabajo.
Excel VBA Events-una guía fácil (y completa).
Manejo de errores de Excel VBA.
Cómo ordenar los datos en Excel usando VBA (una guía paso a paso).
24 ejemplos útiles de Macro de Excel para principiantes de VBA (listos para usar).