VBA Assign Range: Set, Select, and Reference Cells

VBA & macros · Updated November 18, 2026

To assign a cell range to a VBA variable, declare the variable as a Range and use Set, for example: Set rng = ws.Range("A1:B10"). To put a value in cells instead, assign to a range property such as ws.Range("A1").Value; that is a value assignment, not a range-object assignment.

What “assign a range” means in VBA

The phrase can describe two different operations. First, you can make a variable refer to a range object so your code can work with those cells repeatedly. Second, you can write data, formulas, or formatting into cells. Keeping these meanings separate prevents a common VBA error: treating a range object like the value stored in a cell.

A Range object represents one cell or a group of cells on a worksheet. A variable declared As Range can hold a reference to that object. Because a range is an object, VBA uses the Set keyword when assigning it to the variable. By contrast, rng.Value = 25 sets a property of the object: it writes 25 into the cells represented by rng.

For example, Set rng = ws.Range("A1:B10") makes rng refer to twenty cells. It does not change their contents. Once assigned, the variable can be used to read values, write values, format cells, or determine the range’s dimensions.

Basic syntax and a complete example

Here is a small macro that assigns a worksheet range to a variable, writes a heading, and formats the heading cell. The worksheet is explicitly named so the code does not depend on whichever sheet happens to be active.

Sub AssignRangeExample()
    Dim ws As Worksheet
    Dim rng As Range

    ' Point to a worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Report")

    ' Make rng refer to the specified cells.
    Set rng = ws.Range("A1:B10")

    ' Write a value; this does not reassign the range variable.
    ws.Range("A1").Value = "Quarterly report"
    ws.Range("A1").Font.Bold = True
End Sub

Before running any macro that changes a workbook, save a separate copy. VBA macros cannot be undone with Ctrl+Z, so a saved copy gives you a way to recover if the code changes more cells than intended. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11 or choose Developer > Visual Basic. On Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; a function-key modifier may be needed with some keyboard settings. Excel for the web does not run VBA macros.

Qualify the worksheet and workbook

A reference such as Range("A1:B10") is unqualified: VBA looks for the range on the active worksheet. If another sheet becomes active while the macro runs, the code could read or change the wrong cells. It is safer to start with a worksheet variable and qualify the range as ws.Range("A1:B10").

The workbook reference matters too. ThisWorkbook means the workbook that contains the VBA code. ActiveWorkbook means the workbook currently active in Excel, which may be different—for example, when a macro stored in a personal macro workbook operates on another open file. Choose intentionally rather than assuming the two refer to the same workbook.

For maximum clarity, you can assign the workbook and worksheet separately:

Sub ReferenceSpecificWorkbook()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range

    ' This example refers to the workbook containing the code.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rng = ws.Range("C2:C20")
End Sub

If the named sheet does not exist, VBA raises an error when it tries to set ws. Confirm the sheet tab’s name, including spaces, or add error handling if the workbook’s sheet names can vary.

Use Range, Cells, and named ranges

Range is convenient when the cell address is known or can be assembled as text. Cells(row, column) is useful when row or column numbers are calculated. For example, ws.Cells(5, 2) refers to B5. You can also build a range from two cell references, which is handy when its size depends on data:

Sub AssignRangeWithCells()
    Dim ws As Worksheet
    Dim rng As Range
    Dim firstRow As Long
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    firstRow = 2
    lastRow = 20

    ' Both endpoints belong to the same worksheet.
    Set rng = ws.Range(ws.Cells(firstRow, 1), ws.Cells(lastRow, 1))
End Sub

When using Cells inside a range expression, qualify both cell references with the worksheet. Leaving either one unqualified can make the expression depend on the active sheet. A workbook-level or worksheet-level named range is another option: use ws.Range("SalesTotal") when the name is defined for that worksheet, and check the scope if VBA cannot find it.

Assigning values is different from assigning a range

Once a variable refers to cells, use its properties to work with their contents. rng.Value reads or writes the cell value. rng.Formula works with formulas, and properties such as rng.Font.Bold control formatting. These assignments do not replace the object reference held by rng.

For example, if rng already refers to A1, the statement rng.Value = "Ready" writes text into A1. If rng refers to A1:A5, assigning a single value to its Value property writes that value into the cells in the range. To copy cell contents from one range to another without using the clipboard, you can assign the source’s values to the destination, provided the ranges have compatible dimensions:

Sub CopyValuesBetweenRanges()
    Dim ws As Worksheet
    Dim sourceRange As Range
    Dim targetRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    Set sourceRange = ws.Range("A2:A10")
    Set targetRange = ws.Range("C2:C10")

    ' Copy values only; this does not copy formatting.
    targetRange.Value = sourceRange.Value
End Sub

This copies the values, not the source cells’ formatting, column widths, or other features. To copy formulas or formatting, use the appropriate property or a copy method instead. For formula assignments, remember that a formula string must begin with an equals sign, for example rng.Formula = "=SUM(B2:B10)".

Build a range from changing data

Macros often need to select a range whose last row changes as data is added. A common method finds the last nonempty cell in a column by starting at the bottom of the worksheet and moving upward. The following example uses column A and avoids creating a data range if there are no entries beneath the header.

Sub AssignChangingRange()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Include rows below the header only when data exists.
    If lastRow >= 2 Then
        Set dataRange = ws.Range("A2:A" & lastRow)
        Debug.Print dataRange.Address
    End If
End Sub

This approach assumes column A is a reliable indicator of which rows contain records. If that column has gaps, the last row it finds may not represent the end of the whole table. Choose a column that is populated for every record, or use an Excel Table’s DataBodyRange when the data is stored in a table. Also decide how the macro should behave when the table has headers but no data rows.

Other useful range-building tools include Resize, which changes the dimensions of a range, and Offset, which refers to a range shifted from the original. CurrentRegion can return a connected block of data around a starting cell, but blank rows or columns can divide that block. Match the method to the layout rather than assuming all worksheets have one continuous data region.

Choose between a range variable and direct references

A range variable is useful when the same cells are referenced several times, when the address is calculated, or when you want to pass the range to another procedure. For a one-off operation, a direct qualified reference can be simpler. For example, writing to ws.Range("D2").Value once may be clearer than creating a separate variable for that single action.

Use a descriptive variable name such as inputRange or resultsRange when a macro handles several areas. This makes it easier to see which cells a later statement affects. Declare the variable with As Range; declaring it as a Variant instead can obscure whether it contains an object, a value, or an array.

Assigning a range does not select it on the screen. Selection is usually unnecessary for reading, writing, and formatting cells, and avoiding it makes code less dependent on the user interface. If a macro genuinely needs to show a range, activate the relevant worksheet first and then select it; do not use selection as a substitute for qualifying your references.

Common errors and how to fix them

  • Missing Set: Use Set rng = ws.Range("A1") for an object reference. Without Set, VBA treats the assignment differently and may report an object-related error.
  • Wrong active sheet: Replace unqualified Range or Cells references with worksheet-qualified references such as ws.Range and ws.Cells.
  • Misspelled worksheet name: Check the sheet tab and the workbook being referenced. A worksheet lookup by name fails if the name is not present in that workbook.
  • Object variable not set: A declared range variable has no usable range reference until the code successfully assigns one with Set. Check that the assignment runs before any attempt to use the variable.
  • Invalid or unintended address: When building an address from text, check the row and column values, and handle empty data before creating the range. A calculated last row below the expected starting row may produce an incorrect address.
  • Confusing contents with the object: Use Set to change which cells a range variable refers to. Use .Value, .Formula, or another property to change what those cells contain or how they appear.

During debugging, the Immediate window can help confirm what a variable refers to. For example, after setting rng, printing rng.Address lets you inspect the address. Also check the worksheet name and workbook reference before stepping through code that edits data.

Frequently Asked Questions

Do I need Set to assign a range in VBA?
Yes. A Range is an object, so assign it to an object variable with Set, as in Set rng = ws.Range("A1:B10"). You do not use Set when assigning a value to a property such as rng.Value.

What is the difference between Range and Cells?
Range("A1") uses an address written in A1-style notation. Cells(1, 1) uses row and column numbers to refer to the same cell. Both can be qualified with a worksheet variable, and Cells is convenient when the row or column is calculated.

Does assigning a range select it in Excel?
No. Assigning a range variable makes the variable refer to the cells; it does not move the selection or change what is displayed. Most macros can work directly with the reference without selecting cells.

Can I assign a range to a variable without naming a worksheet?
You can write an unqualified reference, but it uses the active worksheet and can act on the wrong sheet if the active tab changes. Using Set rng = ws.Range("A1:B10") makes the target worksheet explicit and is generally safer.

Can I use VBA in Excel for the web?
Excel for the web does not run VBA macros. To run or edit VBA, open the workbook in the Excel desktop application. Excel for Microsoft 365 on Windows and Mac supports VBA, though some features and editor controls can differ by platform.

How do I assign a range that grows with new rows?
Find the last populated row in a dependable column, then build the range using that row number. Check that data exists below any header before assigning the range, and account for gaps or blank rows in the column you use to find the endpoint.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.