VBA Range in Excel: How to Reference Cells

In Excel VBA, a Range is an object that represents one cell or a group of cells. To refer to a cell, use a worksheet’s Range property, such as ws.Range("A1"); you can then read or change its value, format, formula, or other properties. Most macros do not need to select a range first.

What Range Means in VBA

Excel’s VBA object model represents workbook content through objects such as Workbook, Worksheet, and Range. A range can be a single cell, a rectangular block, an entire row or column, or—in some uses—a set of separate areas. Code works with a range by referring to its object and then using a property or method.

For example, Range("A1") identifies cell A1 on the active worksheet. Range("A1:C5") identifies a rectangle of 15 cells. A reference like Columns("B:B") identifies column B, while Rows("2:2") identifies row 2. These are VBA references, not worksheet formulas: they belong in VBA code, usually inside a procedure.

A common misconception is that a macro must select cells before it can change them. Usually, it does not. Referring to the range directly is typically clearer and avoids changing the user’s active selection or relying on which sheet happens to be active.

The Basic Syntax

The Range property is available from a worksheet object. The general pattern is worksheet.Range(address), where the address is usually a string such as "A1" or "B2:D10". In a procedure, assign a worksheet to a variable and use that variable to qualify the range:

Sub WriteToCell()
    Dim ws As Worksheet

    ' Use the Data worksheet in this workbook.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Range("A1").Value = "Ready"
End Sub

Here, Set assigns an object to the worksheet variable. The last line writes text to A1 on the Data sheet. ThisWorkbook means the workbook containing the VBA code; it is different from ActiveWorkbook, which means whichever workbook is currently active in Excel.

For a quick demonstration, place the cursor inside the procedure in the Visual Basic Editor and run it. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11. On Mac, the editor shortcut is commonly Option+F11; a function-key setting may require holding Fn as well. Excel for the web cannot create, edit, or run VBA macros.

Referencing Cells and Building a Range

Use an A1-style address when the location is fixed and easy to read. For a range whose row or column changes as a macro runs, Cells can be more convenient: its arguments are a row number followed by a column number. The following procedure writes to B3, then places a label in a five-cell vertical range:

Sub UseCellsAndRange()
    Dim ws As Worksheet
    Dim target As Range

    ' Address B3 by row and column numbers.
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Cells(3, 2).Value = 42

    ' Make a five-cell range starting at A1.
    Set target = ws.Range("A1").Resize(5, 1)
    target.Value = "Check"
End Sub

Resize(rows, columns) returns a range with the specified dimensions, starting at the original range’s top-left cell. Here, A1 is resized to five rows and one column, so A1:A5 receives the text. Offset(rowChange, columnChange) is another useful range method: it returns a range shifted relative to the original location without changing its size.

You can also pass two cell references to Range to define the corners of a rectangle, as in ws.Range(ws.Cells(2, 1), ws.Cells(6, 3)). That expression represents A2:C6. Combining Cells with Range is helpful when the boundaries come from variables or a loop.

Qualify Ranges With the Right Worksheet

When code uses Range("A1") without naming a worksheet, VBA generally resolves it against the active sheet. This can cause a macro to read from or write to the wrong sheet if the user switches tabs, or if another part of the code activates a different worksheet. Qualifying the reference—ws.Range("A1")—makes the target explicit.

Choose the workbook deliberately too. ThisWorkbook.Worksheets("Data") points to the sheet named Data in the file containing the macro. Workbooks("Report.xlsx").Worksheets("Data") refers to a specifically named open workbook. The latter fails if that workbook is not open or its name differs, so use it only when that condition is expected.

A worksheet variable also avoids repeating long references and makes a procedure easier to maintain. If the worksheet name changes, you can update the setup line rather than every individual cell reference. Remember that worksheet names must match the tab name exactly, including spaces and punctuation.

Read, Write, and Copy Range Values

The Value property reads or changes a cell’s value. Assigning a value to a multi-cell range fills every cell in that range with the assigned value. If you want to transfer a block of values from one range to another, use ranges of matching dimensions:

Sub CopyValuesWithinSheet()
    Dim ws As Worksheet
    Dim source As Range
    Dim destination As Range

    ' Copy cell values, not formatting or formulas.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set source = ws.Range("A2:B6")
    Set destination = ws.Range("D2:E6")
    destination.Value = source.Value
End Sub

Both ranges contain five rows and two columns. The assignment transfers the values currently returned by the source cells; it does not copy their formatting. If the source cells contain formulas, assigning source.Value writes the calculated results into the destination rather than reproducing those formulas. To copy formulas, use the Formula property instead, with care about relative references.

For ordinary cell values, Value is a clear default. Value2 is also widely used for reading and writing values; unlike Value, it does not use VBA’s Currency and Date data subtypes for those cell values. The choice matters in code that relies on specific data types or date and currency conversions, but it is not a different way to address a cell.

When to Select a Range

Range.Select makes a range the current selection in the Excel interface, but it is usually unnecessary for changing data. Direct code such as ws.Range("A1").Value = "Done" is simpler than selecting A1 and then changing ActiveCell. Selection can make a macro dependent on the active sheet and can cause the screen to jump as it runs.

Selection is appropriate when the task specifically involves the visible user interface—for example, when a macro is meant to leave a particular cell highlighted for the user. Even then, the target worksheet must be active before selecting its range. For routine data processing, use a qualified range reference and perform the operation directly.

Some operations are naturally methods of a range, such as clearing contents or applying a format. Treat these as changes to workbook data or appearance, not as harmless selection steps. Before running code that clears or overwrites cells, confirm its target and make a copy of the workbook.

Common Range Errors and How to Avoid Them

Wrong active sheet: An unqualified Range may point to the active worksheet. Store the intended worksheet in a variable and use ws.Range(...).
Misspelled worksheet name: A name that does not exactly match a worksheet tab causes a subscript error. Check spelling and spaces, or use the worksheet’s VBA code name when that is suitable for the project.
Incorrect dimensions: When assigning one range’s values to another, use ranges with compatible row and column counts. A mismatched assignment can produce an error or an unintended result.
Using Select in the wrong context: A worksheet generally must be active before its range can be selected. Avoid selection if the code only needs to read or change cells.
Assuming a range is one cell: A multi-cell range has multiple values. Code that expects a single value should use a single-cell reference or explicitly handle each cell.
Unexpected formula behavior: Assigning Value copies results, not formulas. Use Formula when the destination should contain formulas, and check how relative references will adjust.

When debugging, set a breakpoint by clicking beside a code line in the editor, then run the procedure and inspect variables as it pauses. The Immediate window can also help evaluate a simple expression while code is paused. These tools make it easier to check which worksheet and address a range variable actually represents.

Running VBA Safely in Excel

VBA changes the workbook, and many changes made by a macro cannot be reversed with Excel’s Undo command or Ctrl+Z. Save a separate copy before running an unfamiliar macro, particularly one that deletes, clears, or overwrites data. Review the code’s worksheet and range references before executing it.

Macro availability depends on the file type and security settings. A workbook containing VBA is commonly saved as an Excel Macro-Enabled Workbook (.xlsm); saving it as a macro-free workbook can remove the VBA project. Only enable macros in files from a source you trust. Excel for Mac supports VBA, but code that relies on Windows-specific features, such as certain Windows API calls or external components, may not work on Mac.

Frequently Asked Questions

What is the difference between Range and Cells in VBA?

Range("A1") identifies a cell using its address, while Cells(1, 1) identifies the same cell using row and column numbers. Use Range for readable, fixed addresses and Cells when row or column numbers are calculated in code.

Do I need to select a cell before using Range?

No. You can read or change a qualified range directly, such as ws.Range("A1").Value = 10. Selection is mainly for actions that must visibly change the current selection in Excel.

How do I refer to an entire column or row?

Use a reference such as ws.Columns("B:B") for column B or ws.Rows("2:2") for row 2. Qualifying the reference with the worksheet helps ensure the macro targets the intended sheet.

What is the simplest way to put text in a cell?

Set a worksheet variable, then assign text to the cell’s Value property, as in ws.Range("A1").Value = "Ready". Make sure ws has first been assigned to the intended worksheet with Set.

Can VBA run in Excel for the web?

No. Excel for the web can preserve a workbook that contains VBA, but it cannot create, edit, or run VBA macros. Open the workbook in the Excel desktop application to use VBA.

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.