Rangos y Celdas de Excel VBA

Rangos y Celdas en VBA

Las hojas de cálculo de Excel almacenan los datos en Celdas. Las celdas se organizan en Filas y Columnas. Cada celda puede ser identificada 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 (ej. A3:B4)

Dirección de la celda

Notación A1

En la notación A1, una celda es referida por la letra de su columna (de la A a la XFD) seguida por su número de fila (de 1 a 1,048,576). Esto se denomina dirección de la celda. En VBA puedes referirte a cualquier celda usando el Objeto Rango.

'refiérase a la celda B4 en la hoja actualmente activa
MsgBox Rango("B4")

' Refiérase a la celda B4 de la hoja llamada 'Datos'
MsgBox Worksheets("Data").Range("B4")

' Referirse a la celda B4 de la hoja denominada 'Datos' en otro libro de trabajo ABIERTO
' llamado 'Mis Datos'
MsgBox Workbooks("Mis Datos").Worksheets("Data").Range("B4")

Notación R1C1

En la notación R1C1 una celda es referida por la R seguida del número de fila y luego la letra ‘C’ seguida del número de columna. En VBA se utiliza el objeto Cells para usar la notación R1C1:

'referirse a la celda R[6]C[4], es decir, D6
Cells(6, 4) = "D6"

Rango de Celdas

Notación A1

Para referirse a más de una celda utilice un «:» entre la dirección de la celda inicial y la última. Lo siguiente se referirá a todas las celdas desde A1 hasta D10:

Range("A1:D10")

Notación R1C1

Para referirse a más de una celda, utilice un «,» entre la dirección de la celda inicial y la última. Lo siguiente se referirá a todas las celdas de A1 a D10:

Range(Cells(1, 1), Cells(10, 4))

Escribir en las celdas

Para escribir valores en una celda o grupo contiguo de celdas, basta con referirse al rango, poner un signo = y luego escribir el valor a almacenar:

' Almacenar F6 en la celda con la dirección F6
Range("F6") = "F6"

' Almacenar E6 en la celda con la dirección R[6]C[5] es decir E6
Cells(6, 5) = "E6"

' Almacenar A1:D10 en el rango A1:D10
Range("A1:D10") = "A1:D10"
' o
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

Lectura de celdas

Para leer los valores de las celdas, simplemente haz referencia a la variable para almacenar los valores, pon un signo = y luego haz referencia al rango a leer:

Dim val1
Dim val2

' Leer de la celda F6
val1 = Range("F6")

' Leer de la celda E6
val2 = Cells(6, 5)

MsgBox val1
Msgbox val2

Nota: Para almacenar valores de un rango de celdas, necesitas usar un Array en lugar de una simple variable.

Celdas no contiguas

Para referirse a celdas no contiguas utilice una coma entre las direcciones de las celdas:

' Almacenar 10 en las celdas A1, A3 y A5
Range("A1,A3,A5") = 10


' Almacenar 10 en las celdas A1:A3 y D1:D3) 
Range("A1:A3, D1:D3") = 10

Intersección de celdas

Para referirse a celdas no contiguas utilice un espacio entre las direcciones de las celdas:

' Almacenar 'Col D' en D1:D10
' que es común entre A1:D10 y D1:F10
Range("A1:D10 D1:G10") = "Col D"

Desplazamiento de una celda o rango

Utilizando la función Offset, puede mover la referencia de un Rango dado (celda o grupo de celdas) por el número_de_filas, y número_de_columnas especificado.

Sintaxis de la función Offset

Range.Offset(número_de_filas, número_de_columnas)

Desplazamiento desde una celda

' Desplazamiento desde una celda A1
' Referirse a la propia celda
' Desplazar 0 filas y 0 columnas
Range("A1").Offset(0, 0) = "A1"

' Mover 1 filas y 0 columnas
Range("A1").Offset(1, 0) = "A2"

' Mover 0 filas y 1 columnas
Range("A1").Offset(0, 1) = "B1"

' Mover 1 filas y 1 columnas
Range("A1").Offset(1, 1) = "B2"

' Mover 10 filas y 5 columnas
Range("A1").Offset(10, 5) = "F11"

Desplazamiento de un rango

' Mueve la referencia al rango A1:D4 en 4 filas y 4 columnas
' La nueva referencia es E5:H8
Range("A1:D4").Offset(4,4) = "E5:H8"

Establecer la referencia a un rango

Para asignar un rango a una variable de rango: declare una variable de tipo Rango y luego utilice el comando Set para establecerla en un rango. Tenga en cuenta que debe utilizar el comando SET ya que RANGE es un objeto:

' Declare una variable Range
Dim miRango as Range

' Establece la variable en el rango A1:D4
Set miRango = Range("A1:D4")

' Imprime $A$1:$D$4
MsgBox miRango.Address

Redimensionar un rango

El método Resize del objeto Range cambia la dimensión del rango de referencia:

Sub redimensionar()
    Dim myRange As Range

    ' Rango a redimensionar
    Set myRange = Range("A1:F4")
    
    ' Imprime $A$1:$E$10
    Debug.Print myRange.Resize(10, 5).Address
End Sub

La celda superior izquierda del rango redimensionado es la misma que la celda superior izquierda del rango original

Sintaxis de redimensionamiento

Range.Resize(número_de_filas, número_de_columnas)

OFFSET vs Resize

Offset no cambia las dimensiones del rango, sino que lo desplaza el número de filas y columnas especificado. Redimensionar no cambia la posición del rango original sino que cambia las dimensiones al número de filas y columnas especificado.

Todas las celdas de la hoja

El objeto Cells se refiere a todas las celdas de la hoja (1048576 filas y 16384 columnas).

' Borrar todas las celdas de la hoja
Cells.Clear

UsedRange

La propiedad UsedRange le da el rango rectangular desde la celda superior izquierda usada hasta la celda inferior derecha usada de la hoja activa.

Dim hoja As Worksheet
Set hoja = ActiveSheet

' $B$2:$L$14 si L2 es la primera celda con algún valor 
' y L14 es la última celda con algún valor en la
' hoja activa
Debug.Print hoja.UsedRange.Address

Región Actual

La propiedad CurrentRegion le da el rango rectangular contiguo desde la celda superior izquierda hasta la celda inferior derecha utilizada que contiene la celda/rango referenciado.

Sub regionActual()
    Dim miRango As Range

    Set miRango = Range("D4:F6")

    ' Imprime $B$2:$L$14
    ' Si hay una ruta rellena desde D4:F16 hasta B2 Y L14
    Debug.Print miRango.CurrentRegion.Address
    
    ' Puede referirse a una sola celda de inicio también
    
    Set miRango = Range("D4") ' Imprime $B$2:$L$14
End Sub

Propiedades del Rango

Puede obtener la dirección, el número de fila/columna de una celda y el número de filas/columnas de un rango como se indica a continuación:

Sub filasColumnas()
    Dim miRango As Range

    Set miRango = Range("A1:F10")
    
    'imprime $A$1:$F$10
    Debug.Print miRango.Address
    
    Set miRango = Range("F10")
    
    ' Imprime 10 para la fila 10
    Debug.Print miRango.Row
    
    ' Imprime 6 para la columna F
    Debug.Print miRango.Column
    
    Set miRango = Range("E1:F5")
    ' Imprime 5 para el número de filas en el rango
    Debug.Print miRango.Rows.Count
    
    'imprime 2 para el número de columnas en el rango
    Debug.Print miRango.Columns.Count
End Sub

Última celda de la hoja

Puedes utilizar las propiedades Rows.Count y Columns.Count con el objeto Cells para obtener la última celda de la hoja:

Sub ultima_celda_en_hoja()
    ' Imprime el número de la última fila
    ' Imprime 1048576
    Debug.Print "Filas en la hoja: " & Rows.Count
    
    ' Imprime el último número de columna
    ' Imprime 16384
    Debug.Print "Columnas en la hoja: " & Columns.Count
    
    ' Imprime la dirección de la última celda
    ' Imprime $XFD$1048576
    Debug.Print "Dirección de la última celda de la hoja: " & Cells(Rows.Count, Columns.Count).Address
End Sub

Último número de fila utilizado en una columna

La propiedad END te lleva a la última celda del rango, y End(xlUp) te lleva hasta la primera celda usada desde esa celda.

Dim ultimaFila As Long

ultimaFila = Cells(Rows.Count, "A").End(xlUp).Row

Último número de columna utilizado en una fila

Dim ultimaColumna As Long

ultimaColumna = Cells(1, Columns.Count).End(xlToLeft).Column

La propiedad END te lleva a la última celda del rango, y End(xlToLeft) te lleva a la izquierda a la primera celda usada desde esa celda. También puedes usar las propiedades xlDown y xlToRight para navegar a las primeras celdas usadas abajo o a la derecha de la celda actual.

Propiedades de las celdas

Propiedades comunes

Este es el código para mostrar las propiedades comunes de las celdas

Sub navegarEnPropiedades()
    Dim celda As Range
    Set celda = Range("A1")
    
    celda.Activate
    Debug.Print celda.Address
    ' Imprimir $A$1
    
    Debug.Print celda.Value
    ' Imprime 456
    ' Dirección
    
    Debug.Print celda.Formula
    ' Imprime =SUMA(C2:C3)
    
    ' Comentario
    Debug.Print celda.Comment.Text
    
    ' Estilo
    Debug.Print celda.Style
    
    ' Formato de la celda
    Debug.Print celda.DisplayFormat.NumberFormat
End Sub

Fuente de la Celda

El objeto Cell.Font contiene las propiedades de la fuente de la celda:

Sub fuente()
    Dim celda As Range

    Set celda = Range("A1")
    
    ' Regular, Cursiva, Negrita y Negrita Cursiva
    celda.Font.FontStyle = "Negrita Cursiva"
    ' Igual que
    celda.Font.Bold = True
    celda.Font.Italic = True
    
    ' Establecer la fuente a Courier
    celda.Font.FontStyle = "Courier"
    
    ' Establecer el color de la fuente
    celda.Font.Color = vbBlue
    ' o
    celda.Font.Color = RGB(255, 0, 0)
    
    ' Establece el tamaño de la fuente
    celda.Font.Size = 20
End Sub

Copiar y pegar

Pegar todo

Los rangos/celdas pueden copiarse y pegarse de un lugar a otro. El siguiente código copia todas las propiedades del rango de origen al rango de destino (equivalente a CTRL-C y CTRL-V)

'Copia simple
Range("A1:D20").Copy
Sheets("Hoja2").Range("B10").Paste

o
' Copiar de la hoja actual a la hoja denominada 'Hoja2'
Range("A1:D20").Copy destino:=Sheets("Hoja2").Range("B10")

Pegado especial

Las propiedades seleccionadas del rango de origen pueden ser copiadas al destino utilizando la opción PASTESPECIAL:

' Pegar el rango sólo como Valores
Range("A1:D20").Copy
Worksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues

Estas son las posibles opciones para la opción Pegar:

' Pegar Tipos Especiales
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

Autoajuste de contenido

El tamaño de las filas y columnas puede cambiarse para ajustarse al contenido usando Autoajuste:

' Cambiar el tamaño de las filas 1 a 5 para ajustar el contenido 
Rows("1:5").AutoFit

'cambiar el tamaño de las columnas A a B para ajustar el contenido 
Columns("A:B").AutoFit

Más ejemplos de rangos

Se recomienda utilizar la grabadora de macros mientras se realiza la acción requerida a través de la GUI. Le ayudará a entender las distintas opciones disponibles y cómo utilizarlas.

For Each

Es fácil hacer un bucle a través de un rango usando la construcción For Each como se muestra a continuación:

For Each cell In Range("A1:B100")
   ' Hacer algo con la celda
Next cell

En cada iteración del bucle se asigna una celda del rango a la variable cell y se ejecutan las sentencias del bucle For para esa celda. El bucle sale cuando se procesan todas las celdas.

Ordenar

Sort es un método del objeto Range. Puede ordenar un rango especificando las opciones de ordenación en Range.Sort. El código siguiente ordenará las columnas A:C basándose en la clave de la celda C2. El orden de clasificación puede ser xlAscending o xlDescending. Encabezado:= xlSí debe usarse si la primera fila es la fila de encabezado.

Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlYes

Find

Find es también un método de Range Object. Encuentra la primera celda cuyo contenido coincida con los criterios de búsqueda y devuelve la celda como un objeto Range. No devuelve nada si no hay ninguna coincidencia.

Utilice el método FindNext (o FindPrevious) para encontrar la siguiente ocurrencia (anterior). El siguiente código cambiará la fuente a «Arial Black» para todas las celdas del rango que comienzan con «John»:

For Each c In Range("A1:A100")
    If c Like "John*" Then
        c.Font.Name = "Arial Black"
    End If
Next c

El siguiente código reemplazará todas las apariciones de «a revisar» a «revisado» en el rango especificado:

With Range("a1:a500")
    Set c = .Find("a revisar", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Do
            c.Value = "revisado"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
End With

Es importante tener en cuenta que debe especificar un rango para utilizar FindNext. También debe proporcionar una condición de parada, de lo contrario el bucle 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 se llega a esa celda de nuevo. También debe comprobar el caso de que no se encuentre nada para detener el bucle.

Range Address

Utilice Range.Address para obtener la dirección en el estilo A1

MsgBox Range("A1:D10").Address
' o
Debug.Print Range("A1:D10").Address

Utilice xlReferenceStyle (por defecto es xlA1) para obtener las direcciones en estilo R1C1

MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
' o
Debug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1)

Esto es útil cuando se trata de rangos almacenados en variables y se desea procesar sólo ciertas direcciones.

De rango a matriz

Es más rápido y fácil transferir un rango a un array y luego procesar los valores. Debe declarar el array como Variant para evitar calcular el tamaño requerido para poblar el rango en el array. Las dimensiones del array se ajustan al número de valores del rango.

Dim DirArray As Variant
' almacena los valores del rango en el Array
 
DirArray = Range("a1:a5").Value
 
' Bucle para procesar los valores
For Each c In DirArray
    Debug.Print c
Next

De matriz a rango

Después de procesar el array, puede  escribirlo de nuevo en un rango. Para escribir el Array del ejemplo anterior en un Range debe especificar un Range cuyo tamaño coincida con el número de elementos del Array. Utilice el siguiente código para escribir la matriz en el rango D1:D5:

Range("D1:D5").Value = DirArray
 
Range("D1:H1").Value = Application.Transpose(DirArray)

Tenga en cuenta que debe transponer el Array si lo escribe en una fila.

Suma de Rango

sumaDeRango = Application.WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print sumaDeRango

Puedes utilizar muchas funciones disponibles en Excel en tu código VBA especificando Application. WorkSheetFunction. antes del Nombre de la Función como en el ejemplo anterior.

Contar Rango

' Contar el número de celdas con números en el rango
cuentaDeCeldas= Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print cuentaDeCeldas

' Cuenta el número de celdas que no están en blanco en el rango
cuentadeCeldasNoVacias= Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print cuentadeCeldasNoVacias

Escrito por: Vinamra Chandra