Excel VBA: Use Variables in a Range Statement

VBA & macros · Updated November 19, 2026

Yes. In Excel VBA, pass a variable to Range without putting the variable name in quotation marks, such as Set target = ws.Range(addressText). You can also build a range address from variables, or use Cells(rowNumber, columnNumber) when the row or column is numeric.

The basic syntax

Range identifies a cell or a group of cells using an address. A literal address is written as text, for example Range("B3"). If the address is held in a variable, use the variable itself: Range(addressText). The distinction is important: Range("addressText") asks Excel to find a range literally named addressText, rather than reading the variable’s value.

Because a range is an object, assign it to an object variable with the VBA keyword Set. Declare that variable as Range. This small example puts an address in a string variable and displays the address it refers to:

Sub ShowVariableRange()
    Dim ws As Worksheet
    Dim addressText As String
    Dim target As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    addressText = "B3"
    Set target = ws.Range(addressText)

    MsgBox target.Address
End Sub

Before running a macro, save a copy of the workbook. Macros can change or delete data, and their actions cannot be reversed with Ctrl+Z. This sample only displays an address, but the precaution matters for macros that write to cells.

Build an address from variables

Often the address is not known until the macro runs. Join its parts with the VBA concatenation operator, &. For example, if rowNumber contains 12, then "A" & rowNumber produces the text A12. Assign that text to a string variable or pass the joined expression directly to Range.

Sub ShowCellFromRow()
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim target As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    rowNumber = 12
    Set target = ws.Range("A" & rowNumber)

    MsgBox "The target is " & target.Address(False, False)
End Sub

In this example, the target is cell A12, and the message shows its address without dollar signs. The same idea works for a variable column letter, such as columnLetter = "C", combined with a row number. Keep the resulting text in a valid Excel address format; a misspelled column, an invalid row, or a malformed colon-separated address can cause a run-time error.

Use Cells for numeric row and column values

When both coordinates are numbers, Cells(rowNumber, columnNumber) is often clearer than assembling letters and digits. Row 12, column 3 identifies C12. This is especially useful in loops, where the row changes repeatedly, and it avoids having to convert a column number to a letter.

Sub ShowCellByCoordinates()
    Dim ws As Worksheet
    Dim rowNumber As Long
    Dim columnNumber As Long
    Dim target As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    rowNumber = 12
    columnNumber = 3
    Set target = ws.Cells(rowNumber, columnNumber)

    MsgBox target.Address(False, False)
End Sub

Cells represents a cell when given a row and column, but it can also represent all cells on a worksheet when used without coordinates. You can combine it with Range to define a rectangle by its first and last cells. For example, ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 4)) means rows 2 through lastRow and columns 1 through 4. That covers columns A:D, inclusive.

Qualify the worksheet explicitly

A frequent source of confusing bugs is an unqualified reference such as Range(addressText). Without a worksheet qualifier, VBA uses the active sheet in the context where the code runs. If a user clicks a different tab before running the macro, the code may act on the wrong sheet. Use a worksheet variable and qualify the reference: ws.Range(addressText) or ws.Cells(rowNumber, columnNumber).

For a worksheet in the workbook containing the macro, ThisWorkbook.Worksheets("Data") explicitly identifies the sheet named Data. ActiveWorkbook instead refers to whichever workbook is active, which may not be the workbook that contains the macro. Choose deliberately, particularly if a macro opens or works with multiple files. A sheet name must match the tab name exactly; otherwise, VBA raises an error when it tries to assign the worksheet.

Qualifying the range does not automatically make every operation valid. For instance, selecting a cell on an inactive worksheet can fail because selection depends on the active sheet. If the goal is to read or write data, it is usually better to work directly with the qualified range’s properties than to select it first.

Find the last row and create a changing range

A common task is to process a data block whose length changes. The following example finds the last non-empty cell in column A, then defines a range from A2 through column D of that row. It checks that column A is not completely empty before constructing the range.

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

    Set ws = ThisWorkbook.Worksheets("Data")

    If Application.WorksheetFunction.CountA(ws.Columns(1)) = 0 Then
        MsgBox "Column A is empty."
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "There are no data rows below the header."
        Exit Sub
    End If

    Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 4))
    MsgBox "Range found: " & dataRange.Address(False, False)
End Sub

If row 1 is a header and the last filled cell in column A is A20, the range is A2:D20. This approach assumes column A is populated for each relevant data row. If that is not true, choose a column that reliably marks the end of the data, or use a different method suited to the worksheet. A formatted but otherwise blank sheet, gaps in the chosen column, and data beginning on a different row can affect what “last row” means for a particular task.

Choose the right variable type

Use String for a complete address such as "B2:D20", or for a column letter such as "C". Use Long for row and column numbers. Excel worksheets can have more rows than fit in VBA’s older Integer type, so a Long is the suitable choice for row indexes. Use Range for an object that refers to one cell or a block of cells.

Declare variables with Dim, and consider putting Option Explicit at the top of a module. It requires variables to be declared and helps catch typos such as using lastrow in one place and lastRow in another. VBA is not case-sensitive, but a misspelled name can still accidentally become a separate variable when declarations are not enforced.

Keep address text and range objects conceptually separate. A string such as "B2" is text describing a location; a Range variable refers to the actual Excel object. Use Set to assign the object, then use properties such as .Value2, .Address, or .ClearContents as appropriate. For example, reading target.Value2 returns the cell’s underlying value; assigning to it changes the cell, so confirm that the target is correct first.

Common errors and how to fix them

  • Putting the variable name in quotes: Range("addressText") treats the text as a literal reference or name. Use Range(addressText) when the variable contains the address.
  • Leaving off Set: Assigning a range object with target = ws.Range(addressText) is not the correct object assignment. Write Set target = ws.Range(addressText).
  • Using an invalid address: Check the value of the string before passing it to Range. A missing row number or a misspelled address can produce run-time error 1004.
  • Relying on the active sheet: An unqualified Range can refer to a different sheet than intended. Create a worksheet variable and use ws.Range or ws.Cells.
  • Confusing a cell with a range address: Cells(5, 2) means B5. The row comes first and the column second, so reversing the arguments changes the destination.
  • Using Select unnecessarily: Directly read or update the range object instead of selecting it first. Selection adds dependence on which sheet and workbook are active.

When an error occurs, use the VBA editor’s debugging tools to inspect the variable values and the highlighted line. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11 or use Developer > Visual Basic. On Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11. The Developer tab may need to be enabled in Excel’s ribbon settings. Excel for the web does not run VBA macros, so use the desktop application for this workflow.

When to use Range versus Cells

Use Range with a string when the address is already in A1-style form, such as a user-provided address or a range assembled from text. Use Cells when the row and column are numeric, especially in loops or when calculating positions. Both can describe the same location, so the choice is mainly about which form makes the code easier to read and validate.

For a rectangular range, using the two-cell form ws.Range(firstCell, lastCell) is often safer than manually joining a string with a colon. For example, if the boundaries are calculated as row and column numbers, ws.Range(ws.Cells(firstRow, firstColumn), ws.Cells(lastRow, lastColumn)) keeps those numbers numeric throughout. This also makes it easier to adjust the start and end columns without converting numbers into letters.

Frequently Asked Questions

Can I put a variable directly inside the Range statement?
Yes. Use ws.Range(addressText) if the variable contains a complete address, or build the address inside the call, as in ws.Range("A" & rowNumber). Do not put quotation marks around the variable name itself.

How do I use a variable for the row number?
For a row in column A, use ws.Cells(rowNumber, 1) or assemble the address with ws.Range("A" & rowNumber). The first option treats the row and column as numeric coordinates.

Do I need Set when assigning a Range variable?
Yes. A range is an object, so use Set target = ws.Range(addressText). You do not use Set when assigning an ordinary value to a cell, such as target.Value2 = "Done".

Why does Range(variable) work on the wrong worksheet?
An unqualified Range depends on the active sheet. Assign the intended sheet to a worksheet variable and use ws.Range(variable). This makes the reference independent of which tab the user last selected.

Can Excel for the web run a VBA macro?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel application on Windows or Mac to run VBA; behavior involving platform-specific features may differ between those desktop versions.

What should I do if my variable-built address gives error 1004?
Inspect the final string before using it and confirm that it is a valid address on the intended worksheet. Check for missing row numbers, incorrect separators, and unexpected blank values, then qualify the range with the correct worksheet.

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.