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. UseRange(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. WriteSet 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
Rangecan refer to a different sheet than intended. Create a worksheet variable and usews.Rangeorws.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
- Excel Spreadsheet Keyboard Shortcuts: A Practical Guide
- Excel Data Validation Lists: Create and Manage Dropdowns
- Excel Shuffle List of Names: 10 Easy Methods
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Reset AutoFilter: Clear Filters, Keep the SetupTo reset an Excel AutoFilter in VBA while keeping its dropdown arrows, clear the active filter rather than…
- Excel VBA Substring: Extract, Find, and Replace TextExcel VBA has no built-in function named Substring, but you can work with part of a string using…
- Sort Columns in Excel with VBAYes. In Excel VBA, use the Sort object to sort entire rows by values in a chosen column,…
- VBA Code to Copy and Paste Values in ExcelTo copy a range’s results without its formulas, use VBA’s PasteSpecial with xlPasteValues; for a fast transfer between…
- How to Turn Off a TinyTask Macro and Stop PlaybackTo stop a TinyTask macro that is playing, press TinyTask’s Play/stop hotkey—Ctrl+Shift+Alt+P by default—or click the Play button.…
- Excel to PowerPoint VBA: How to Automate SlidesExcel VBA can automate PowerPoint on Windows: select worksheet ranges, copy them, create slides, and paste the content…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.