Rangos y celdas en VBA
Las hojas de cálculo de Excel almacenan datos en celdas. Las celdas se organizan en filas y columnas. Cada celda se puede identificar por el punto de intersección de su fila y columna (Ej. B3 o R3C2).
Un rango de Excel se refiere a una o más celdas (por ejemplo, A3: B4)
Dirección de celda
Notación A1
En la notación A1, se hace referencia a una celda por la letra de la columna (de la A a la XFD) seguida de su número de fila (de 1 a 1.048.576).
En VBA puede hacer referencia a cualquier celda usando el Objeto de rango.
| 123456789 | 'Consulte la celda B4 en la hoja actualmente activaRango de MsgBox ("B4")'Consulte la celda B4 en la hoja denominada' Datos 'MsgBox Worksheets ("Datos"). Rango ("B4")'Consulte la celda B4 en la hoja llamada' Datos 'en otro libro ABIERTO'llamado' Mis datos 'MsgBox Workbooks ("Mis datos"). Hojas de trabajo ("Datos"). Rango ("B4") |
Notación R1C1
En la notación R1C1, una celda se denomina R seguida del número de fila, luego la letra "C" seguida del número de columna. Por ejemplo, B4 en la notación R1C1 será referido por R4C2. En VBA usas el Objeto de celdas para usar la notación R1C1:
| 12 | 'Consulte la celda R [6] C [4], es decir, D6Celdas (6, 4) = "D6" |
Rango de celdas
Notación A1
Para hacer referencia a más de una celda, utilice un “:” entre la dirección de la celda inicial y la dirección de la última celda. Lo siguiente se referirá a todas las celdas de A1 a D10:
| 1 | Rango ("A1: D10") |
Notación R1C1
Para hacer referencia a más de una celda, use un "," entre la dirección de la celda inicial y la dirección de la última celda. Lo siguiente se referirá a todas las celdas de A1 a D10:
| 1 | Rango (celdas (1, 1), celdas (10, 4)) |
Escribir en celdas
Para escribir valores en una celda o grupo contiguo de celdas, simplemente consulte el rango, coloque un signo = y luego escriba el valor que se almacenará:
| 12345678910 | 'Almacene F5 en la celda con la dirección F6Rango ("F6") = "F6"'Almacene E6 en la celda con la dirección R [6] C [5], es decir, E6Celdas (6, 5) = "E6"'Almacenar A1: D10 en el rango A1: D10Rango ("A1: D10") = "A1: D10"' oRango (celdas (1, 1), celdas (10, 4)) = "A1: D10" |
Lectura de celdas
Para leer los valores de las celdas, simplemente consulte la variable para almacenar los valores, coloque un signo = y luego consulte el rango a leer:
| 1234567891011 | Dim val1Dim val2'Leer de la celda F6val1 = Rango ("F6")'Leer de la celda E6val2 = Celdas (6, 5)MsgBox val1Msgbox val2 |
Nota: para almacenar valores de un rango de celdas, debe usar una matriz en lugar de una variable simple.
Células no contiguas
Para hacer referencia a celdas no contiguas, use una coma entre las direcciones de celda:
| 123456 | 'Almacene 10 en las celdas A1, A3 y A5Rango ("A1, A3, A5") = 10'Almacene 10 en las celdas A1: A3 y D1: D3)Rango ("A1: A3, D1: D3") = 10 |
Intersección de celdas
Para hacer referencia a celdas no contiguas, use un espacio entre las direcciones de celda:
| 123 | 'Almacenar' Col D 'en D1: D10'que es común entre A1: D10 y D1: F10Rango ("A1: D10 D1: G10") = "Col D" |
Desplazamiento desde una celda o rango
Con la función de compensación, puede mover la referencia de un rango determinado (celda o grupo de celdas) por el número_de_fileras especificado y el número_de_columnas.
Sintaxis de compensación
Rango.Desplazamiento (número_de_fileras, número_de_columnas)
Desplazamiento de una celda
| 12345678910111213141516 | 'DESPLAZAMIENTO de una celda A1'Referirse a la propia celda'Mover 0 filas y 0 columnasRango ("A1"). Desplazamiento (0, 0) = "A1"'Mover 1 filas y 0 columnasRango ("A1"). Desplazamiento (1, 0) = "A2"'Mover 0 filas y 1 columnasRango ("A1"). Desplazamiento (0, 1) = "B1"'Mover 1 filas y 1 columnasRango ("A1"). Desplazamiento (1, 1) = "B2"'Mueve 10 filas y 5 columnasRango ("A1"). Desplazamiento (10, 5) = "F11" |
Desplazamiento de un rango
| 123 | 'Mover la referencia al rango A1: D4 en 4 filas y 4 columnasLa nueva referencia es E5: H8Rango ("A1: D4"). Desplazamiento (4,4) = "E5: H8" |
Establecer referencia a un rango
Para asignar un rango a una variable de rango: declare una variable de tipo Rango y luego use el comando Establecer para establecerlo en un rango. Tenga en cuenta que debe utilizar el comando SET ya que RANGE es un objeto:
| 12345678 | 'Declarar una variable de rangoAtenuar myRange como rango'Establezca la variable en el rango A1: D4Establecer myRange = Range ("A1: D4")'Imprime $ A $ 1: $ D $ 4MsgBox myRange.Address |
Cambiar el tamaño de un rango
El método de cambio de tamaño del objeto Rango cambia la dimensión del rango de referencia:
| 1234567 | Atenuar myRange como rango'Rango para cambiar el tamañoEstablecer myRange = Range ("A1: F4")'Imprime $ A $ 1: $ E $ 10Debug.Print myRange.Resize (10, 5) .Address |
La celda superior izquierda del rango redimensionado es la misma que la celda superior izquierda del rango original
Cambiar el tamaño de la sintaxis
Range.Resize (número_de_fileras, número_de_columnas)
OFFSET vs Redimensionar
El desplazamiento no cambia las dimensiones del rango, sino que lo mueve el número especificado de filas y columnas. Redimensionar no cambia la posición del rango original, pero cambia las dimensiones al número especificado de filas y columnas.
Todas las celdas de la hoja
El objeto Cells se refiere a todas las celdas de la hoja (1048576 filas y 16384 columnas).
| 12 | 'Borrar todas las celdas en hojas de trabajoCells.Clear |
Rango usado
La propiedad UsedRange le proporciona el rango rectangular desde la celda usada de la celda superior izquierda hasta la celda usada inferior derecha de la hoja activa.
| 1234567 | Dim ws como hoja de trabajoEstablecer ws = ActiveSheet'$ B $ 2: $ L $ 14 si L2 es la primera celda con cualquier valor'y L14 es la última celda con cualquier valor en el' hoja activaDebug.Print ws.UsedRange.Address |
Región actual
La propiedad CurrentRegion le proporciona el rango rectangular contiguo desde la celda superior izquierda hasta la celda utilizada inferior derecha que contiene la celda / rango referenciado.
| 1234567891011 | Atenuar myRange como rangoEstablecer myRange = Range ("D4: F6")'Imprime $ B $ 2: $ L $ 14'Si hay una ruta llena de D4: F16 a B2 Y L14Debug.Print myRange.CurrentRegion.Address'También puede referirse a una sola celda inicialSet myRange = Range ("D4") 'Imprime $ B $ 2: $ L $ 14 |
Propiedades de rango
Puede obtener la dirección, el número de fila / columna de una celda y el número de filas / columnas en un rango como se indica a continuación:
| 123456789101112131415161718192021 | Atenuar myRange como rangoEstablecer myRange = Range ("A1: F10")'Imprime $ A $ 1: $ F $ 10Debug.Print myRange.AddressEstablecer myRange = Range ("F10")'Imprime 10 para la Fila 10Debug.Print myRange.Row'Imprime 6 para la columna FDebug.Print myRange.ColumnEstablecer myRange = Range ("E1: F5")'Imprime 5 para el número de filas en el rangoDebug.Print myRange.Rows.Count'Imprime 2 para el número de columnas en el rangoDebug.Print myRange.Columns.Count |
Última celda de la hoja
Puedes usar Filas Cuenta y Columnas Cuenta propiedades con Células objeto para obtener la última celda de la hoja:
| 1234567891011 | 'Imprime el último número de fila'Imprime 1048576Debug.Print "Filas en la hoja:" & Rows.Count'Imprime el último número de columna'Imprime 16384Debug.Print "Columnas en la hoja:" & Columns.Count'Imprime la dirección de la última celda'Impresiones $ XFD $ 1048576Debug.Print "Dirección de la última celda en la hoja:" & Celdas (Rows.Count, Columns.Count) |
Último número de fila utilizado en una columna
La propiedad END le lleva a la última celda del rango, y End (xlUp) le lleva hasta la primera celda utilizada de esa celda.
| 123 | Dim lastRow tan largolastRow = Celdas (Rows.Count, "A"). End (xlUp) .Row |
Último número de columna utilizado en una fila
| 123 | Dim lastCol As LonglastCol = Celdas (1, Columns.Count) .End (xlToLeft) .Column |
La propiedad END lo lleva a la última celda del rango, y End (xlToLeft) lo lleva a la izquierda a la primera celda utilizada de esa celda.
También puede usar las propiedades xlDown y xlToRight para navegar a las primeras celdas usadas inferior o derecha de la celda actual.
Propiedades de la celda
Propiedades Comunes
Aquí hay un código para mostrar las propiedades de celda de uso común
| 12345678910111213141516171819202122 | Atenuar celda como rangoEstablecer celda = Rango ("A1")Cell.ActivateDebug.Print celda.Address'Imprimir $ A $ 1Debug.Print cell.Value'Grabados 456' DirecciónDepurar Imprimir celda Fórmula'Impresiones = SUMA (C2: C3)ComentarioDebug.Print celda.Comment.Text' EstiloDebug.Print cell.Style'Formato de celdaDebug.Print celda.DisplayFormat.NumberFormat |
Fuente de celda
El objeto Cell.Font contiene propiedades de Cell Font:
| 1234567891011121314151617181920 | Atenuar celda como rangoEstablecer celda = Rango ("A1")'Regular, cursiva, negrita y negrita cursivacell.Font.FontStyle = "Negrita cursiva"' Igual quecell.Font.Bold = Verdaderocell.Font.Italic = True'Establecer fuente en Couriercell.Font.FontStyle = "Mensajero"'Establecer color de fuentecell.Font.Color = vbBlue' ocell.Font.Color = RGB (255, 0, 0)'Establecer tamaño de fuentecell.Font.Size = 20 |
Copiar y pegar
Pegar todo
Los rangos / celdas se pueden copiar y pegar de una ubicación a otra. El siguiente código copia todas las propiedades del rango de origen en el rango de destino (equivalente a CTRL-C y CTRL-V)
| 1234567 | 'Copia simpleRango ("A1: D20"). CopiarHojas de trabajo ("Hoja2"). Rango ("B10"). Pegar'o'Copiar de la hoja actual a la hoja denominada' Hoja2 'Rango ("A1: D20"). Destino de la copia: = Hojas de trabajo ("Hoja2"). Rango ("B10") |
Pegado especial
Las propiedades seleccionadas del rango de origen se pueden copiar al destino mediante la opción PASTESPECIAL:
| 123 | 'Pegue el rango solo como valoresRango ("A1: D20"). CopiarHojas de trabajo ("Hoja2"). Rango ("B10"). PasteSpecial Paste: = xlPasteValues |
Estas son las posibles opciones para la opción Pegar:
| 12345678910111213 | 'Pegar tipos especialesxlPasteAllxlPasteAllExceptBordersxlPasteAllMergingConditionalFormatsxlPasteAllUsingSourceThemexlPasteColumnWidthsxlPasteCommentsxlPasteFormatsxlPasteFormulasxlPasteFormulasAndNumberFormatsxlPasteValidationxlPasteValuesxlPasteValuesAndNumberFormats |
Contenidos de Autoajuste
El tamaño de las filas y columnas se puede cambiar para adaptarse al contenido usando el siguiente código:
| 12345 | 'Cambiar el tamaño de las filas 1 a 5 para adaptarse al contenidoFilas ("1: 5"). Ajuste automático'Cambiar el tamaño de las columnas A a B para que se ajusten al contenidoColumnas ("A: B"). Autoajuste |
Más ejemplos de rango
Se recomienda que utilice la Grabadora de macros mientras realiza la acción requerida a través de la GUI. Le ayudará a comprender las distintas opciones disponibles y cómo utilizarlas.
Para cada
Es más fácil recorrer un rango usando Para cada construir como se muestra a continuación:
| 123 | Para cada celda del rango ("A1: B100")'Haz algo con el celularSiguiente celda |
En cada iteración del bucle, se asigna una celda del rango a la variable cy las instrucciones del bucle For se ejecutan para esa celda. El bucle sale cuando se procesan todas las celdas.
Clasificar
Sort es un método de objeto Range. Puede ordenar un rango especificando opciones para ordenar en Range.Sort. El siguiente código ordenará las columnas A: C según la clave en la celda C2. El orden de clasificación puede ser xlAscending o xlDescending. Encabezado: = xlYes debe usarse si la primera fila es la fila del encabezado.
| 12 | Columnas ("A: C"). Ordenar clave1: = Rango ("C2"), _order1: = xlAscending, Header: = xlYes |
Encontrar
Find también es un método de Range Object. Encuentra la primera celda que tiene contenido que coincide con los criterios de búsqueda y devuelve la celda como un objeto Range. Vuelve Nada si no hay coincidencia.
Usar FindNext método (o FindPrevious) para encontrar la siguiente (anterior) ocurrencia.
El siguiente código cambiará la fuente a "Arial Black" para todas las celdas en el rango que comienzan con "John":
| 12345 | Para cada c dentro del rango ("A1: A100")Si c como "John *", entoncesc.Font.Name = "Arial Black"Terminara siSiguiente c |
El siguiente código reemplazará todas las apariciones de "Para probar" por "Aprobado" en el rango especificado:
| 12345678910 | Con rango ("a1: a500")Establecer c = .Find ("Para probar", LookIn: = xlValues)Si no c no es nada entoncesfirstaddress = c.AddressHacerc.Value = "Aprobado"Establecer c = .FindNext (c)Bucle mientras no c no es nada y c. Dirección primera direcciónTerminara siTerminar con |
Es importante tener en cuenta que debe especificar un rango para usar FindNext. También debe proporcionar una condición de detención, de lo contrario, el ciclo se ejecutará para siempre. Normalmente, la dirección de la primera celda que se encuentra se almacena en una variable y el bucle se detiene cuando llega a esa celda nuevamente. También debe verificar el caso cuando no se encuentra nada para detener el bucle.
Dirección de rango
Utilice Range.Address para obtener la dirección en estilo A1
| 123 | Rango de MsgBox ("A1: D10"). Dirección' oDebug.Print Range ("A1: D10"). Dirección |
Use xlReferenceStyle (el predeterminado es xlA1) para obtener direcciones en estilo R1C1
| 123 | MsgBox Range ("A1: D10"). Dirección (ReferenceStyle: = xlR1C1)' oDebug.Print Range ("A1: D10"). Dirección (ReferenceStyle: = xlR1C1) |
Esto es útil cuando se trata de rangos almacenados en variables y desea procesar solo para ciertas direcciones.
Rango a matriz
Es más rápido y más fácil transferir un rango a una matriz y luego procesar los valores. Debe declarar la matriz como Variante para evitar calcular el tamaño requerido para completar el rango en la matriz. Las dimensiones de la matriz están configuradas para coincidir con el número de valores en el rango.
| 123456789 | Dim DirArray como variante'Almacene los valores en el rango a la matrizDirArray = Rango ("a1: a5"). Valor'Bucle para procesar los valoresPara cada c en DirArrayDebug.Print cpróximo |
Matriz a rango
Después del procesamiento, puede volver a escribir la matriz en un rango. Para escribir la matriz en el ejemplo anterior en un rango, debe especificar un rango cuyo tamaño coincida con el número de elementos en la matriz.
Utilice el siguiente código para escribir la matriz en el rango D1: D5:
| 123 | Rango ("D1: D5"). Valor = DirArrayRango ("D1: H1"). Valor = Aplicación.Transponer (DirArray) |
Tenga en cuenta que debe transponer la matriz si la escribe en una fila.
Rango suma
| 12 | SumOfRange = Application.WorksheetFunction.Sum (Range ("A1: A10"))Debug.Print SumOfRange |
Puede usar muchas funciones disponibles en Excel en su código VBA especificando Application.WorkSheetFunction. antes del Nombre de la función como en el ejemplo anterior.
Rango de conteo
| 1234567 | 'Contar el número de celdas con números en el rangoCountOfCells = Application.WorksheetFunction.Count (Range ("A1: A10"))Debug.Print CountOfCells'Cuenta el número de celdas que no están en blanco en el rangoCountOfNonBlankCells = Application.WorksheetFunction.CountA (Range ("A1: A10"))Debug.Print CountOfNonBlankCells |
Escrito por: Vinamra Chandra
