A VBA Excel Range variable is an object variable that refers to one or more worksheet cells, so you can read or change those cells through code. Declare it with As Range, then assign a worksheet range to it with Set; for example, Dim dataRange As Range followed by Set dataRange = Worksheets("Sheet1").Range("A1:B5").
What a Range variable represents
In VBA, a variable can hold a value, such as a number or text, or it can refer to an object, such as a worksheet, workbook, or cell range. A variable declared As Range refers to a Range object. That object can represent one cell, a rectangular block, a full row or column, or a group of separate areas.
The variable is a reference to cells in the workbook, not a separate copy of their contents. If dataRange refers to A1:B5, changing dataRange.Value changes those worksheet cells. If you instead copy a cell’s value into a Variant variable, that variable holds the value at the time it was read; it is not a live reference back to the cell.
Use a Range variable when the same cells need to be addressed several times, when a range is calculated at runtime, or when code will work with a selection as a unit. It makes procedures easier to read than repeating long expressions such as a workbook, worksheet, and address in every statement.
How to declare and assign a Range variable
Place declarations near the start of a procedure. With Option Explicit at the top of the module, VBA requires every variable to be declared, which helps catch misspelled names before a macro runs. For a Range object, the declaration uses Dim and the type Range. Assigning an object requires Set; omitting it is a common cause of compile errors.
Before running a macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z. The following example applies a number format to a fixed range. It assumes the workbook containing the macro has a worksheet named Input.
Option Explicit
Sub FormatInputBlock()
Dim ws As Worksheet
Dim dataRange As Range
' Refer to the sheet and its data block.
Set ws = ThisWorkbook.Worksheets("Input")
Set dataRange = ws.Range("B2:D10")
' Apply a format to all cells in the block.
dataRange.NumberFormat = "0.00"
End Sub
ThisWorkbook means the workbook that contains the VBA code. That may differ from ActiveWorkbook, which means the workbook currently active in Excel. Using ThisWorkbook is often safer for a macro stored in the workbook it is intended to update. If the target is another workbook, identify that workbook explicitly rather than relying on whichever one happens to be active.
Qualify the worksheet and workbook
A reference such as Range("A1") does not say which worksheet it belongs to. In a standard module, an unqualified Range call is generally resolved against the active sheet, so the result can change when a user clicks another tab. Qualifying the range with its worksheet makes the target clear and avoids accidental edits to the wrong sheet.
Assign a worksheet variable first, then use its Range or Cells member. The worksheet reference itself should also be qualified by a workbook. For example, ThisWorkbook.Worksheets("Sales").Range("A1") identifies one location without depending on the active sheet. Worksheet tab names used in the code must match actual sheet names; a missing name raises a subscript-out-of-range error.
For an external workbook, a robust pattern is to set a Workbook variable to the intended open workbook, then set the Worksheet variable from it. Be aware that Workbooks("Report.xlsx") refers to an open workbook by its name; it does not open the file. If the workbook is not already open, the macro must open it or use a different approach.
Read values and change cell properties
A Range variable exposes both the cells’ values and other properties, including formulas, formatting, and dimensions. Use the property that matches the task. For example, dataRange.Value reads or writes the range’s values, while dataRange.Formula deals with formulas. Value2 is often used when transferring cell values to VBA because it avoids the Currency and Date subtypes returned by Value.
When a range has several cells, assigning one value to its Value property fills every cell in the range with that value. Reading a multi-cell range into a Variant typically produces a two-dimensional array, indexed by row and column. Code that expects one cell’s scalar value should therefore make sure the range is a single cell.
Do not confuse the object variable with a value variable. To refer to a range, use Set; to store a cell’s value, use ordinary assignment without Set. This distinction reflects VBA’s difference between object references and values. A cell’s Interior, Font, and EntireRow members are also objects, while a property such as Value2 returns data.
Build a range whose size changes
Many macros process a changing amount of data, so a fixed address such as A2:A100 may leave out new rows or include blank ones. One common pattern finds the last used row in a particular column with End(xlUp), then constructs a Range from the first data row to that last row. This assumes column A is populated for every relevant record and that row 1 contains a header.
Save a workbook copy before running this example as well. It reads the values of the detected range into a Variant array; it does not alter the worksheet.
Option Explicit
Sub ReadColumnAData()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range
Dim values As Variant
' Find the last nonblank cell in column A.
Set ws = ThisWorkbook.Worksheets("Sales")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Stop if there are no data rows below the header.
If lastRow < 2 Then Exit Sub
' Refer to the data cells, excluding the header.
Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 1))
values = dataRange.Value2
End Sub
The code uses Cells(row, column), with numeric column 1 meaning column A. Cells returns a Range object too, which is why the complete range is assigned with Set. If blank cells can occur inside the data or column A is not a dependable marker, choose a more suitable column or define the data boundaries another way. A worksheet’s used range can include cells that were formatted or edited in the past, so it is not always a reliable substitute for a deliberate last-row rule.
Use With blocks and work with multiple ranges
A With block is useful after assigning a range variable when several operations target that same object. Inside the block, a leading period refers to the object named after With. This reduces repetition, but every dotted member inside the block must belong to the intended object; a missing period can accidentally refer to something else.
A Range variable can also refer to multiple areas, such as A1:A3 and C1:C3 together. Such a range is called a multiple-area range. Some operations work across all areas, while others expect a contiguous block or behave differently when given multiple areas. Check the behavior of the specific property or method before treating a multi-area range like a rectangle.
For noncontiguous selections, Areas provides access to each individual block. A loop over those blocks can make intent explicit. For ordinary contiguous data, however, one rectangular Range variable is simpler and generally easier to process in an array.
Common Range variable errors
When VBA reports an error involving a Range variable, check how it was declared, assigned, and qualified. An object variable that has not been assigned is Nothing; trying to use one of its properties causes an “Object variable or With block variable not set” error. A misspelled worksheet name or a reference to a workbook that is not open can cause a different runtime error.
- Forgot
Set: UseSet rangeVariable = worksheet.Range("A1")to assign an object reference. Ordinary assignment is for values. - Used the wrong active sheet: Replace unqualified calls such as
Range("A1")with a qualified expression such asws.Range("A1"). - Never assigned the variable: Ensure every code path assigns the Range before using it. When a reference may intentionally be absent, test
If dataRange Is Nothing Thenbefore accessing it. - Confused a value with a range:
Setassigns object references, not a cell’s text or number. Use normal assignment to read a value into a Variant or another appropriate type. - Used a fixed address for changing data: Calculate the boundary from the worksheet contents, and decide how headers, blank rows, and empty sheets should be handled.
To intentionally clear an object reference, use Set dataRange = Nothing. This is rarely necessary for a local variable that goes out of scope when its procedure ends, but it can be useful when a longer-running procedure should release a reference early. It does not erase the cells; it only disconnects that variable from the object.
Range variables compared with Cells and addresses
Range and Cells are not competing data types: both expressions return Range objects. Range("B2:D10") is convenient when an address is known in A1 notation. Cells(rowNumber, columnNumber) is useful when row or column numbers are calculated, and it can be combined with a second cell to define a rectangular range.
A string variable that stores "B2:D10" is only text until it is used to obtain a Range object. Likewise, an address such as dataRange.Address describes the location but is not itself the Range variable. Choose a Range variable when subsequent statements need to work with the actual cells; choose a string when you only need to store or display an address.
Frequently Asked Questions
How do I declare a Range variable in VBA?
Use Dim myRange As Range inside a procedure or at module level as appropriate. Assign the cells it should refer to with Set myRange = worksheet.Range("A1:B5").
Why does a Range variable need Set?
A Range is an object, and VBA uses Set to assign an object reference. You do not use Set when assigning a cell’s value to a normal variable, such as a Variant.
How can I refer to a cell on a specific worksheet?
Qualify the range with a worksheet reference, for example Set myRange = ThisWorkbook.Worksheets("Sheet1").Range("A1"). This avoids relying on the active sheet.
Can a Range variable refer to one cell?
Yes. A Range object can refer to a single cell, such as A1, or to many cells. The type remains Range in either case.
How do I check whether a Range variable has been assigned?
Compare it with Nothing, for example If myRange Is Nothing Then. Assign the range before accessing its properties or methods.
Editing and running VBA in Excel
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic if the Developer tab is visible. On a Mac, use Tools > Macro > Visual Basic Editor; the Option+F11 shortcut may require the Fn key depending on the keyboard and system settings. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros, so use the desktop application for these examples.
Macros may be blocked by security settings, and a workbook must be saved in a macro-enabled format such as .xlsm to retain VBA code. Only enable macros in workbooks from sources you trust. Before running code that changes cells, keep a backup copy, because Excel’s Undo command does not reverse macro actions.
Related
- Resolving Circular References in Excel
- Repeat Print Area in Excel: Repeat Rows on Every Page
- Remove Text to Columns in Excel: Undo or Reverse It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Get File Name: Workbook, Path, and FolderIn Excel VBA, use ThisWorkbook.Name for the name of the workbook containing the macro, or ActiveWorkbook.Name for the…
- Access VBA Match Function: What It Does and How to Use ItMicrosoft Access does not provide Excel’s worksheet MATCH function as a native VBA function. To find a value…
- VBA Array Length: Count Elements and Find BoundsIn VBA, calculate an array’s length as UBound(array) - LBound(array) + 1. The expression works for a one-dimensional…
- VBA Move UserForm Without a Mouse: Keyboard MethodsYes. In Excel VBA, move a UserForm in code by changing its Left and Top properties; to move…
- Excel VBA: Run a Macro Automatically When a Workbook OpensYes. To run a VBA macro when an Excel workbook opens, put a Workbook_Open event procedure in that…
- VBA Dictionary Documentation: Setup, Methods, Properties & ExamplesFor VBA Dictionary documentation, start with Microsoft’s Dictionary object reference: it describes the Scripting.Dictionary object, which stores unique…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.