VBA Range Cells

What is Excel VBA Range Cells?

The VBA Range Cells property is a powerful feature used to manipulate and work with specific cells within a worksheet. This property allows you to refer to individual or group cells within a range. The VBA Range Cells property is part of the Range object, which is fundamental in Excel VBA for handling cell data, formatting, and other operations.

Let us look at an example. In this example, we have a subroutine called FormatCellBasedOnCondition. It illustrates how to use Excel’s VBA Range Cells property to format a specific cell conditionally. Suppose we have an Excel worksheet where the value in cell A1 is 25, and we want to use the Range cells property to change the color; we can achieve this using the following code:

When you run this code, it dynamically checks the value in cell A1 of the “Sheet1” worksheet. If the value is greater than 10, it changes the background color of that cell to green; otherwise, it leaves the cell’s formatting unchanged.

Syntax of RANGE object

RangeObject.Cells(row, column)

The syntax for the VBA Range Cells property is as follows:

• RangeObject: This represents the range you want to work with.
• row: Specifies the row number within the range.
• column: Specifies the column number within the range.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to use Range Cells in VBA?

Here are the steps to use the VBA Range Cells property:

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Inside the VBA editor, go to Insert – Module to insert a new module where you can write your VBA code.

Step 3: Within the new module, declare a variable of type Worksheet. This variable will represent the specific worksheet you want to work with. For example:

Dim ws As Worksheet

Step 4: Use the Set keyword to reference the chosen worksheet from your workbook to the variable you declared. To reference “Sheet1” within the current workbook, you can use this code as an example:

Set ws = ThisWorkbook.Sheets(“Sheet1”)

Step 5: With the worksheet reference in place, you can now utilize the VBA Range Cells property to interact with specific cells within that worksheet. The syntax generally involves specifying the cell’s location like this:

ws.Range(“A1”).Cells(1, 1)

This line of code refers to cell A1 within the worksheet, represented by ws.

Step 6: Once you’ve identified the desired cell using VBA Range Cells, you can perform various actions. These actions may include setting the cell’s value, applying formatting, or implementing conditional logic to modify its content.

For example, here’s how you can set the value of a cell:

ws.Range(“A1”).Cells(1, 1).Value = “Hello, World!”

Step 7: Save the macro and exit the VBA. Click on Run to execute your macro.

Examples

Example #1 – Inserting Values to Cells

This example inserts names into cells A2 and A3 of “Sheet1.”

Step 1: Define a VBA subroutine named InsertValuesToCells in the new module.

Step 2: Now, declare a variable ws of type Worksheet. This variable will be used to store a reference to the worksheet you want to work with.

Step 3: Use the Set keyword to assign the worksheet named “Sheet1” from the current workbook (referenced by ThisWorkbook) to the ws variable.

Step 4: You set cell values inside the subroutine using the VBA Range Cells property.

• The first line sets the value of cell A1 (the first cell in column A) on the “Sheet1” worksheet to “Name.”
• The second sets the value of cell A2 to “John.”
• The third line sets the value of cell A3 to “Alice.”

In each of these lines, we first specify the worksheet using ws, then the Range property to select a specific cell, and finally, the Cells property to specify the exact cell within the range.

Step 5: Next, ensure you save the module and close the VBE.

In your Excel worksheet, activate the Macros window by pressing Alt + F8. Then, choose the “InsertValuesToCells” macro from the list and initiate it by clicking the Run button.

Step 6: Upon execution of the code, it will fill cells A1, A2, and A3 within the “Sheet1” worksheet with the values “Name,” “John,” and “Alice,” respectively.

Here is the full code:

Sub InsertValuesToCells()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
‘ Set values in cells
ws.Range(“A1”).Cells(1, 1).Value = “Name”
ws.Range(“A2”).Cells(1, 1).Value = “John”
ws.Range(“A3”).Cells(1, 1).Value = “Alice”
End Sub

Example #2 – Reference cells from Another Sheet

This example shows how to copy the value from cell B2 in “SourceSheet” to cell A1 in “TargetSheet.”

Step 1: Define a VBA subroutine called ReferenceFromAnotherSheet in the new module.

Step 2: Declare two variables, wsSource and wsTarget, both of type, Worksheet. These variables will store references to the source and target worksheets.

Step 3: Use the Set keyword to assign the worksheets “SourceSheet” and “TargetSheet” from the current workbook to the wsSource and wsTarget variables, respectively.

Step 4: Within the subroutine, you copy a value from one sheet to another using the VBA Range Cells Copy property.

Explanation:

• wsTarget.Range(“A1”).Cells(1, 1).Value: This part selects cell A1 on the target worksheet (wsTarget) using the Range and Cells properties and sets its value.

=: This is the assignment operator.

• wsSource.Range(“B2”).Cells(1, 1).Value: This part selects cell B2 on the source worksheet (wsSource) and retrieves its value. This value is then assigned to cell A1 on the target worksheet.

Step 5: Now, save the module and exit the VBE. In the Excel worksheet, press Alt + F8 to open the Macros window and then select “ReferenceFromAnotherSheet” and run the macro.

Step 6: When the code is executed, it copies the value from cell B2 of the “SourceSheet” worksheet to cell A1 of the “TargetSheet” worksheet within the same workbook.

Here is the full code:

Sub ReferenceFromAnotherSheet()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Set wsSource = ThisWorkbook.Sheets(“SourceSheet”)
Set wsTarget = ThisWorkbook.Sheets(“TargetSheet”)
‘ Copy value from one sheet to another
wsTarget.Range(“A1”).Cells(1, 1).Value = wsSource.Range(“B2”).Cells(1, 1).Value
End Sub

Example #3 – Reference Cells from Another Workbook:

This example shows how to reference cells from a different workbook (“SourceWorkbook.xlsx”) and copy data to the current workbook.

Step 1: Define a VBA subroutine named ReferenceFromAnotherWorkbook in the new module.

Step 2: Now, declare three variables: wbSource of type Workbook, wsSource of type Worksheet, and wsTarget of type Worksheet. These variables will store references to the source workbook, source worksheet, and target worksheet.

Step 3: Use the Set keyword to open the source workbook (“SourceWorkbook.xlsx”) and assign it to the wbSource variable.

Step 4: Assign the “Sheet1” worksheet from the source workbook to the wsSource variable and the “TargetSheet” worksheet from the current workbook to the wsTarget variable using the Set keyword.

Step 5: Within the subroutine, copy a value from a cell in the source workbook to a cell in the current workbook:

This line of code is like Example #2. It copies the value from cell B2 in the source worksheet to cell A1 in the target worksheet.

Step 6: Close the source workbook using the Close method.

It ensures that the source workbook is closed after the data is copied.

Step 7: Now, save the module and exit the VBE. In the Excel worksheet, press Alt + F8 to open the VBA Macros window and select “ReferenceFromAnotherWorkbook” and run the code.

Step 8: When the code is executed, it opens a workbook named “SourceWorkbook.xlsx,” copies the value from cell B2 of the “Sheet1” worksheet in that source workbook to cell A1 of the “TargetSheet” worksheet in the current workbook, and then closes the source workbook.

Here is the full code:

Sub ReferenceFromAnotherWorkbook()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Set wbSource = Workbooks.Open(“SourceWorkbook.xlsx”)
Set wsSource = wbSource.Sheets(“Sheet1”)
Set wsTarget = ThisWorkbook.Sheets(“TargetSheet”)
‘ Copy value from another workbook to the current workbook
wsTarget.Range(“A1”).Cells(1, 1).Value = wsSource.Range(“B2”).Cells(1, 1).Value
‘ Close the source workbook
wbSource.Close
End Sub

Example #4 – Use the word “SET” for Range Object

This example demonstrates using the Set keyword to assign a range object before setting its value.

Step 1: In the new module, define a VBA subroutine named UsingSetKeyword.

Step 2: Next, we declare a variable ws of type Worksheet to store a reference to the worksheet you want to work with.

Step 3: Use the Set keyword to assign the “Sheet1” worksheet from the current workbook to the ws variable.

Step 4: Declare a variable rng of type Range. This variable will store a reference to a specific cell within the worksheet.

Step 5: Use the Set keyword again to assign the cell A1 from the ws worksheet to the rng variable. This line creates a range object that refers to cell A1.

Step 6: Set the value of the “rng” cell to “Using Set.” This line assigns the specified value to cell A1 on the “Sheet1” worksheet.

In this example, we use the Set keyword twice: first to assign the worksheet to the ws variable and then to assign the cell to the rng variable. This illustrates how to create and work with range objects in VBA.

Step 7: Now, save the module and exit the VBE. In the Excel worksheet, press Alt + F8 to open the Macros window and then select “UsingSetKeyword” and click on run.

Step 8: When the code is executed, it assigns the “Sheet1” worksheet in the current workbook to the variable ws, then uses the Set keyword to create a range object rng that refers to cell A1 within that worksheet. Finally, it sets the value of cell A1 to “Using Set.”

Here is the full code:

Sub UsingSetKeyword()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(“Sheet1”)
‘ Use the Set keyword to assign a range object
Dim rng As Range
Set rng = ws.Range(“A1”).Cells(1, 1)
‘ Set the value of the cell
rng.Value = “Using Set”
End Sub

Important Things To Note

1. You can use the VBA Range Cells property to manipulate individual cells within a range or to traverse through cells in a loop.
2. The VBA Range Cells Select property selects and references specific cells within a worksheet.
3. You can access and manipulate the values within specific cells with the VBA Range Cells Value.
4. VBA Range Cells Copy helps copy data between cells or ranges within the same worksheet or across different worksheets/workbooks.
5. The VBA Range Cells Address property provides the cell’s address in a specified format (e.g., A1, $A$1, etc.).

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Range Cells, with its syntax and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Range Cells. We learn how to use range cells in excel VBA with its syntax, examples & points to remember. You can learn more from the following articles –

• VBA UsedRange
• VBA Sort Range
• VBA Selection Range