Cuando trabaja con Excel, pasa la mayor parte del tiempo en el área de la hoja de trabajo, tratando 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 puede hacer con rangos en VBA (como seleccionar, copiar, mover, editar, etc.).
Entonces, para cubrir este tema, dividiré este tutorial en secciones y le mostraré cómo trabajar con celdas y rangos en Excel VBA usando ejemplos.
Empecemos.
Todos los códigos que menciono en este tutorial deben colocarse en el Editor de VB. Vaya a la sección "Dónde colocar el código VBA" para saber cómo funciona.Si está interesado en aprender VBA de forma sencilla, consulte mi Capacitación en línea de Excel VBA.
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 lea esta sección y comprenda cómo funciona. Esto será crucial en su aprendizaje de VBA y muchos de los conceptos cubiertos aquí se utilizarán a lo largo de este tutorial.
Entonces, comencemos con un ejemplo muy simple.
Seleccionar una celda individual usando VBA
Si desea seleccionar una sola celda en la hoja activa (por ejemplo, A1), puede usar el siguiente código:
Sub SelectCell () Range ("A1"). Seleccione End 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.
Range ("A1") le dice a VBA la dirección de la celda a la que queremos hacer referencia.
Seleccione es un método del objeto Range y selecciona las celdas / rango especificado en el objeto Range. Las referencias de celda deben estar entre comillas dobles.
Este código mostraría 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 mucho. Como 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"). Active Range ("A1"). Seleccione End Sub
De manera similar, 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"). Active Range ("A1"). Seleccione End Sub
Tenga en cuenta que cuando se refiere a libros de trabajo, debe utilizar el nombre completo junto con la extensión del archivo (.xlsx en el código anterior). En caso de que el libro de trabajo nunca se haya guardado, no es necesario que use 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ía 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.
Selección de un rango de tamaño fijo
Aquí está el código que seleccionará el rango A1: D20.
Sub SelectRange () Range ("A1: D20"). Seleccione End Sub
Otra forma de hacer esto es usando el siguiente código:
Sub SelectRange () Range ("A1", "D20"). Seleccione End 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 resulta útil cuando se trabaja con rangos de tamaño variable (como veremos cuando se trate la propiedad End más adelante en este tutorial).
Si desea que la selección se realice en un libro de trabajo diferente o en una hoja de trabajo diferente, debe indicarle 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"). Active Range ("A1: D20"). Seleccione End Sub
Ahora, ¿qué pasa si no sabe cuántas filas hay? ¿Qué sucede si desea seleccionar todas las celdas que tienen un valor?
En estos casos, debe utilizar los métodos que se muestran en la siguiente sección (sobre la selección de un rango de tamaño variable).
Selección de un rango de tamaño variable
Hay diferentes formas de seleccionar un rango de celdas. El método que elija dependerá de cómo estén estructurados los datos.
En esta sección, cubriré algunas técnicas útiles que son realmente útiles cuando trabaja 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 () Rango ("A1"). CurrentRegion.Select End 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 a continuación, 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 Worksheet.
Seleccionar usando la propiedad UsedRange
UsedRange le permite hacer referencia a cualquier celda que haya sido modificada.
Entonces, el siguiente código seleccionaría todas las celdas utilizadas en la hoja activa.
Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub
Tenga en cuenta que en caso de que tenga una celda lejana que se haya utilizado, el código anterior lo consideraría y se seleccionarían todas las celdas hasta la celda utilizada.
Seleccionar usando la propiedad final
Ahora bien, esta parte es realmente útil.
La propiedad Fin le permite seleccionar la última celda llena. Esto le permite imitar el efecto de la tecla de flecha Control hacia abajo / arriba o las teclas Control derecha / izquierda.
Intentemos comprender esto con un ejemplo.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea seleccionar rápidamente las últimas celdas llenas en la columna A.
El problema aquí es que los datos pueden cambiar y no sabe cuántas celdas están llenas. Si tiene que hacer esto usando el teclado, puede seleccionar la celda A1, y luego usar Control + tecla de flecha abajo, y seleccionará la última celda llena de 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 () Rango ("A1"). Fin (xlDown) .Seleccione End Sub
El código anterior saltaría a la última celda llena en la columna A.
De manera similar, puede usar End (xlToRight) para saltar a la última celda llena de una fila.
Sub GoToLastFilledCell () Rango ("A1"). End (xlToRight) .Seleccione End Sub
Ahora, ¿qué sucede si desea seleccionar toda la columna en lugar de saltar a la última celda llena?
Puedes hacerlo usando el siguiente código:
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Seleccione End Sub
En el código anterior, hemos utilizado la primera y la última referencia de la celda que debemos 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 del rango. Podemos usar la misma lógica para seleccionar rangos de tamaño variable. Pero como no conocemos la dirección exacta de la celda inferior derecha, usamos la propiedad End para obtenerla.
En Range ("A1", Range ("A1"). End (xlDown)), "A1" se refiere a la primera celda y Range ("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 llenas a partir de la celda A1.
Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Seleccione End Sub
En el código anterior, hemos utilizado Range ("A1"). End (xlDown) .End (xlToRight) para obtener la referencia de la celda rellena de la parte inferior derecha del conjunto de datos.
Diferencia entre usar CurrentRegion y End
Si se pregunta por qué usar la propiedad End para seleccionar el rango completo cuando tenemos la propiedad CurrentRegion, permítame decirle 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 (usando el código a continuación).
Sub SelectFilledCells () Range ("A2", Range ("A2"). End (xlDown) .End (xlToRight)). Seleccione End Sub
Pero CurrentRegion seleccionaría automáticamente todo el conjunto de datos, incluidos los encabezados.
Hasta ahora en este tutorial, hemos visto cómo hacer referencia a un rango de celdas usando diferentes formas.
Ahora veamos algunas formas en las que realmente podemos usar estas técnicas para hacer algo de trabajo.
Copiar celdas / rangos usando VBA
Como mencioné al comienzo 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.
Copia de una celda
Si desea copiar la celda A1 y pegarla en la celda D1, el siguiente código lo haría.
Sub CopyCell () Rango ("A1"). Copiar rango ("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 usa el método Copiar. Si desea que su código sea aún más legible, puede usar el siguiente código:
Sub CopyCell () Rango ("A1"). Destino de copia: = Rango ("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á cualquier código existente en la celda D2. Si desea que Excel le avise si ya hay algo en la celda D1 sin sobrescribirlo, puede usar el siguiente código.
Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("¿Desea sobrescribir los datos existentes", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copiar rango ("D1 ") End If End Sub
Copia de un rango de tamaño fijo
Si desea copiar A1: D20 en J1: M20, puede usar el siguiente código:
Sub CopyRange () Rango ("A1: D20"). Copiar rango ("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"). Copiar hojas de trabajo ("Sheet2"). Range ("A1") End Sub
Lo anterior copia los datos de la hoja activa. Así que asegúrese de que la hoja que tiene los datos sea 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 () Hojas de trabajo ("Hoja1"). Rango ("A1: D20"). Copiar hojas de trabajo ("Hoja2"). Rango ("A1") Fin 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 en Sheet2.
Sub CopyRange () Range ("SalesData"). Copiar hojas de trabajo ("Sheet2"). Range ("A1") End Sub
Si el alcance del rango con nombre es todo el libro de trabajo, no es necesario que esté en la hoja que tiene el rango con nombre para ejecutar este código. Dado que el rango con nombre tiene el alcance del libro de trabajo, puede acceder a él desde cualquier hoja con 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 [#All]"). Copiar hojas de trabajo ("Sheet2"). Range ("A1") End Sub
También puede copiar un rango a otro libro de trabajo.
En el siguiente ejemplo, copio la tabla de Excel (Table1) en el libro de trabajo Book2.
Sub CopyCurrentRegion () Range ("Table1 [#All]"). Copiar libros de trabajo ("Book2.xlsx"). Worksheets ("Sheet1"). Range ("A1") End Sub
Este código funcionaría solo si el libro de trabajo ya está abierto.
Copia de un rango de tamaño variable
Una forma de copiar rangos de tamaño variable es convertirlos en rangos con nombre o en una 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 End del objeto de rango.
El siguiente código copiaría la región actual en la hoja activa y la pegaría en Sheet2.
Sub CopyCurrentRegion () Rango ("A1"). CurrentRegion. Copiar hojas de trabajo ("Hoja2"). Rango ("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)). Copiar hojas de trabajo ("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)). Copiar hojas de trabajo ("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 usando la dirección completa de las celdas (como Libros de trabajo (“Libro2.xlsx”). Hojas de trabajo (“Hoja1”). Rango (“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 las variables de objeto y luego utilicé estas variables para copiar datos de un rango a otro.
Sub CopyRange () Atenuar SourceRange como rango Atenuar DestinationRange como rango Establecer SourceRange = Hojas de trabajo ("Hoja1"). Rango ("A1: D20") Establecer rango de destino = Hojas de trabajo ("Hoja2"). Rango ("A1") Rango de origen. Copiar rango de destino End Sub
Comenzamos declarando las variables como objetos Range. Luego asignamos el rango a estas variables usando la instrucción Set. Una vez que se ha asignado el rango a la variable, simplemente puede usar la variable.
Ingrese datos en la siguiente celda vacía (usando el cuadro de entrada)
Puede utilizar los cuadros de entrada para permitir que el usuario introduzca los datos.
Por ejemplo, suponga 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.
Sub EnterData () Dim RefRange As Range Establecer RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Establecer ProductCategory = RefRange.Offset (0, 1) Establecer Cantidad = RefRange.Offset (0, 2 ) Set Amount = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Categoría de producto") Cantidad.Value = InputBox ("Cantidad") Amount.Value = InputBox ("Cantidad") End Sub
El código anterior usa el cuadro de entrada VBA para obtener las entradas del usuario y luego ingresa las entradas en las celdas especificadas.
Tenga en cuenta que no usamos referencias de celda exactas. En su lugar, hemos utilizado la propiedad End and 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 monto, 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 hemos 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 puede resultar útil cuando desee analizar cada celda y realizar alguna acción en función de ella.
Por ejemplo, si desea resaltar cada tercera fila en la selección, entonces necesita recorrer y verificar el número de fila. Del mismo modo, si desea resaltar todas las celdas negativas cambiando el color de la 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á filas alternativas.
Sub HighlightAlternateRows () Atenuar Myrange como rango Atenuar Myrow como rango Establecer Myrange = Selección para cada Myrow en Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
El código anterior usa 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 en el que el código pasa por cada celda y resalta las celdas que tienen un valor negativo.
Sub HighlightAlternateRows () Atenuar Myrange como rango Atenuar Mycell como rango Establecer Myrange = Selección para cada Mycell en Myrange Si Mycell <0 Entonces Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Tenga en cuenta que puede hacer lo mismo con el formato condicional (que es dinámico y una mejor manera de hacerlo). 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
¿Se pregunta dónde va el código VBA en su libro de Excel?
Excel tiene un backend de VBA llamado editor de VBA. Debe copiar y pegar el código en la ventana de código del módulo VB Editor.
Estos son los pasos para hacer esto:
- Vaya a la pestaña Desarrollador.
- Haga clic en la opción de Visual Basic. Esto abrirá el editor de VB en el backend.
- En el panel Explorador de proyectos en el Editor de VB, haga clic con el botón derecho en cualquier objeto del libro en el que desea 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.
