Excel VBA Ranges and Cells

Ranges and Cells in VBA

Excel spreadsheets store data in Cells. Cells are arranged into Rows and Columns. Each cell can be identified by the intersection point of it’s row and column (Exs. B3 or R3C2).

An Excel Range refers to one or more cells (ex. A3:B4)

Cell Address

A1 Notation

In A1 notation, a cell is referred to by it’s column letter (from A to XFD) followed by it’s row number(from 1 to 1,048,576). This is called a cell address.

In VBA you can refer to any cell using the Range Object.

' Refer to cell B4 on the currently active sheet
MsgBox Range("B4")

' Refer to cell B4 on the sheet named 'Data'
MsgBox Worksheets("Data").Range("B4")

' Refer to cell B4 on the sheet named 'Data' in another OPEN workbook
' named 'My Data'
MsgBox Workbooks("My Data").Worksheets("Data").Range("B4")

R1C1 Notation

In R1C1 Notation a cell is referred by R followed by Row Number then letter ‘C’ followed by the Column Number. eg B4 in R1C1 notation will be referred by R4C2. In VBA you use the Cells Object to use R1C1 notation:

' Refer to cell R[6]C[4] i.e D6
Cells(6, 4) = "D6"

Range of Cells

A1 Notation

To refer to a more than one cell use a “:” between the starting cell address and last cell address. The following will refer to all the cells from A1 to D10:

Range("A1:D10")

R1C1 Notation

To refer to a more than one cell use a “,” between the starting cell address and last cell address. The following will refer to all the cells from A1 to D10:

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

Writing to Cells

To write values to a cell or contiguous group of cells, simple refer to the range, put an = sign and then write the value to be stored:

' Store F5 in cell with Address F6
Range("F6") = "F6"

' Store E6 in cell with Address R[6]C[5] i.e E6
Cells(6, 5) = "E6"

' Store A1:D10 in the range A1:D10
Range("A1:D10") = "A1:D10"
' or
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

Reading from Cells

To read values from cells, simple refer to the variable to store the values, put an = sign and then refer to the range to be read:

Dim val1
Dim val2

' Read from cell F6
val1 = Range("F6")

' Read from cell E6
val2 = Cells(6, 5)

MsgBox val1
Msgbox val2

Note: To store values from a range of cells, you need to use an Array instead of a simple variable.

Non Contiguous  Cells

To refer to non contiguous  cells use a comma between the cell addresses:

' Store 10 in cells A1, A3, and A5
Range("A1,A3,A5") = 10


' Store 10 in cells A1:A3 and D1:D3) 
Range("A1:A3, D1:D3") = 10

Intersection of  Cells

To refer to non contiguous  cells use a space between the cell addresses:

' Store 'Col D' in D1:D10
' which is Common between A1:D10 and D1:F10
Range("A1:D10 D1:G10") = "Col D"

Offset from a Cell or Range

Using the Offset function, you can move the reference from a given Range (cell or group of cells) by the specified number_of_rows, and number_of_columns.

Offset Syntax

Range.Offset(number_of_rows, number_of_columns)

Offset from a cell

' OFFSET from a cell A1
' Refer to cell itself
' Move 0 rows and 0 columns
Range("A1").Offset(0, 0) = "A1"

' Move 1 rows and 0 columns
Range("A1").Offset(1, 0) = "A2"

' Move 0 rows and 1 columns
Range("A1").Offset(0, 1) = "B1"

' Move 1 rows and 1 columns
Range("A1").Offset(1, 1) = "B2"

' Move 10 rows and 5 columns
Range("A1").Offset(10, 5) = "F11"

Offset from a Range

' Move Reference to Range A1:D4 by 4 rows and 4 columns
' New Reference is E5:H8
Range("A1:D4").Offset(4,4) = "E5:H8"

Setting Reference to a Range

To assign a range to a range variable: declare a variable of type Range then use the Set command to set it to a range. Please note that you must use the SET command as RANGE is an object:

' Declare a Range variable
Dim myRange as Range

' Set the variable to the range A1:D4
Set myRange = Range("A1:D4")

' Prints $A$1:$D$4
MsgBox myRange.Address

Resize a Range

Resize method of Range object changes the dimension of the reference range:

Dim myRange As Range

' Range to Resize
Set myRange = Range("A1:F4")

' Prints $A$1:$E$10
Debug.Print myRange.Resize(10, 5).Address

Top-left cell of the Resized range is same as the top-left cell of the original range

Resize Syntax

Range.Resize(number_of_rows, number_of_columns)

OFFSET vs Resize

Offset does not change the dimensions of the range but moves it by the specified number of rows and columns. Resize does not change the position of the original range but changes the dimensions to the specified number of rows and columns.

All Cells in Sheet

The Cells object refers to all the cells in the sheet (1048576 rows and 16384 columns).

' Clear All Cells in Worksheets
Cells.Clear

UsedRange

UsedRange property gives you the rectangular range from the top-left cell used cell to the right-bottom used cell of the active sheet.

Dim ws As Worksheet
Set ws = ActiveSheet

' $B$2:$L$14 if L2 is the first cell with any value 
' and L14 is the last cell with any value on the
' active sheet
Debug.Print ws.UsedRange.Address

CurrentRegion

CurrentRegion property gives you the contiguous rectangular range from the top-left cell to the right-bottom used cell containing the referenced cell/range.

Dim myRange As Range

Set myRange = Range("D4:F6")

' Prints $B$2:$L$14
' If there is a filled path from D4:F16 to B2 AND L14
Debug.Print myRange.CurrentRegion.Address

' You can refer to a single starting cell also

Set myRange = Range("D4") ' Prints $B$2:$L$14

Range Properties

You can get Address, row/column number of a cell, and number of rows/columns in a range as given below:

Dim myRange As Range

Set myRange = Range("A1:F10")

' Prints $A$1:$F$10
Debug.Print myRange.Address

Set myRange = Range("F10")

' Prints 10 for Row 10
Debug.Print myRange.Row

' Prints 6 for Column F
Debug.Print myRange.Column

Set myRange = Range("E1:F5")
' Prints 5 for number of Rows in range
Debug.Print myRange.Rows.Count

' Prints 2 for number of Columns in range
Debug.Print myRange.Columns.Count

Last Cell in Sheet

You can use Rows.Count and Columns.Count properties with Cells object to get the last cell on the sheet:

' Print the last row number
' Prints 1048576
Debug.Print "Rows in the sheet: " & Rows.Count

' Print the last column number
' Prints 16384
Debug.Print "Columns in the sheet: " & Columns.Count

' Print the address of the last cell
' Prints $XFD$1048576
Debug.Print "Address of Last Cell in the sheet: " & Cells(Rows.Count, Columns.Count)

Last Used Row Number in a Column

END property takes you the last cell in the range, and End(xlUp) takes you up to the first used cell from that cell.

Dim lastRow As Long

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

Last Used Column Number in a Row

Dim lastCol As Long

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

END property takes you the last cell in the range, and End(xlToLeft) takes you left to the first used cell from that cell.

You can also use xlDown and xlToRight properties to navigate to the first bottom or right used cells of the current cell.

Cell Properties

Common Properties

Here is code to display commonly used Cell Properties

Dim cell As Range
Set cell = Range("A1")

cell.Activate
Debug.Print cell.Address
' Print $A$1

Debug.Print cell.Value
' Prints 456
' Address

Debug.Print cell.Formula
' Prints =SUM(C2:C3)

' Comment
Debug.Print cell.Comment.Text

' Style
Debug.Print cell.Style

' Cell Format
Debug.Print cell.DisplayFormat.NumberFormat

Cell Font

Cell.Font object contains properties of the Cell Font:

Dim cell As Range

Set cell = Range("A1")

' Regular, Italic, Bold, and Bold Italic
cell.Font.FontStyle = "Bold Italic"
' Same as
cell.Font.Bold = True
cell.Font.Italic = True

' Set font to Courier
cell.Font.FontStyle = "Courier"

' Set Font Color
cell.Font.Color = vbBlue
' or
cell.Font.Color = RGB(255, 0, 0)

' Set Font Size
cell.Font.Size = 20

Copy and Paste

Paste All

Ranges/Cells can be copied and pasted from one location to another. The following code copies all the properties of source range to destination range (equivalent to CTRL-C and CTRL-V)

'Simple Copy
Range("A1:D20").Copy 
Worksheets("Sheet2").Range("B10").Paste

'or
' Copy from Current Sheet to sheet named 'Sheet2'
Range("A1:D20").Copy destination:=Worksheets("Sheet2").Range("B10")

Paste Special

Selected properties of the source range can be copied to the destination by using PASTESPECIAL option:

' Paste the range as Values only
Range("A1:D20").Copy
Worksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues

Here are the possible options for the Paste option:

' Paste Special Types
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

AutoFit Contents

Size of rows and columns can be changed to fit the contents using AutoFit:

' Change size of rows 1 to 5 to fit contents 
Rows("1:5").AutoFit

' Change size of Columns A to B to fit contents 
Columns("A:B").AutoFit

More Range Examples

It is recommended that you use Macro Recorder while performing the required action through the GUI. It will help you understand the various options available and how to use them.

For Each

It is easy to loop through a range using For Each construct as show below:

For Each cell In Range("A1:B100")
    ' Do something with the cell
Next cell

At each iteration of the loop one cell in the range is assigned to the variable cell and statements in the For loop are executed for that cell. Loop exits when all the cells are processed.

Sort

Sort is a method of Range object. You can sort a range by specifying options for sorting to Range.Sort. The code below will sort the columns A:C based on key in cell C2. Sort Order can be xlAscending or xlDescending. Header:= xlYes should be used if first row is the header row.

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

Find

Find is also a method of Range Object. It find the first cell having content matching the search criteria and returns the cell as a Range object. It return Nothing if there is no match.

Use FindNext method (or FindPrevious) to find next(previous) occurrence.

Following code will change the font to “Arial Black” for all cells in the range which start with “John”:

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

Following code will replace all occurrences of  “To Test” to “Passed” in the range specified:

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

It is important to note that you must specify a range to use FindNext. Also you must provide a stopping condition otherwise the loop will execute forever. Normally address of the first cell which is found is stored in a variable and loop is stopped when you reach that cell again. You must also check for the case when nothing is found to stop the loop.

Range Address

Use Range.Address to get the address in A1 Style

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

Use xlReferenceStyle (default is xlA1) to get addres in R1C1 style

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

This is useful when you deal with ranges stored in variables and want to process for certain addresses only.

Range to Array

It is faster and easier to transfer a range to an array and then process the values. You should declare the array as Variant to avoid calculating the size required to populate the range in the array. Array’s dimensions are set to match number of values in the range.

Dim DirArray As Variant
' Store the values in the range to the Array

DirArray = Range("a1:a5").Value

' Loop to process the values
For Each c In DirArray
    Debug.Print c
Next

Array to Range

After processing you can write the Array back to a Range. To write the Array in the example above to a Range you must specify a Range whose size matches the number of elements in the Array.

Use the code below to write the Array to the range D1:D5:

Range("D1:D5").Value = DirArray 

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

Please note that you must Transpose the Array if you write it to a row.

Sum Range

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

You can use many functions available in Excel in your VBA code by specifying Application.WorkSheetFunction. before the Function Name as in the example above.

Count Range

' Count Number of Cells with Numbers in the Range
CountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print CountOfCells

' Count Number of Non Blank Cells in the Range
CountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print CountOfNonBlankCells

Written by: Vinamra Chandra