An Excel macro range is one cell or a group of cells that VBA can read, change, format, or otherwise work with. In Excel for Microsoft 365 on Windows, refer to a range with a worksheet-qualified expression such as ThisWorkbook.Worksheets("Data").Range("A1:B10"); qualifying the sheet helps ensure the macro acts on the intended cells.
What “range” means in an Excel macro
In VBA, a range is represented by Excel’s Range object. It can refer to a single cell, a rectangular block, a named range, or—in some cases—a group of separate areas. The object is the way a macro identifies the cells it needs to inspect or change. A range is not limited to cells that already contain data: Range("A1:B10") still identifies all 20 cells even if they are blank.
Range references use the familiar worksheet address style. For example, Range("C5") means one cell, and Range("A2:D12") means a rectangle whose corners are A2 and D12. The address is interpreted relative to a worksheet, so the macro should specify that worksheet rather than rely on whichever sheet happens to be active.
A macro can use a range to read or write values, clear contents, apply formatting, find the last populated row, or pass cells to another operation. The same address may be fixed in the code or built while the macro runs, depending on whether the data always occupies the same cells.
Basic VBA range syntax
The Range property belongs to a worksheet. A clear, reliable pattern is to assign the worksheet to a variable and then use that variable for every range operation. This avoids an easy-to-miss problem: an unqualified Range("A1") refers to the active sheet, which may not be the sheet the macro author intended.
For example, the following macro clears contents in B2:D20 on a sheet named Input. It leaves formatting in place. Before running any macro that changes or clears cells, save a separate copy of the workbook: VBA changes cannot be reversed with Ctrl+Z.
Sub ClearInputRange()
Dim ws As Worksheet
'Use the worksheet in this workbook, not the active sheet.
Set ws = ThisWorkbook.Worksheets("Input")
ws.Range("B2:D20").ClearContents
End Sub
ThisWorkbook means the workbook containing the VBA code. If the macro is stored in a personal macro workbook or an add-in but should edit a different workbook, identify that target workbook explicitly instead. A worksheet name that does not exist causes a run-time error, so check spelling, spaces, and whether the tab has been renamed.
Choosing between Range and Cells
Use Range when the cell address is easy to read or is already known, such as ws.Range("A1:C10"). Use Cells when the row or column number is calculated, or when a loop moves through cells. A worksheet’s Cells(row, column) property uses numeric row and column indexes: row 1, column 1 is A1; row 3, column 2 is B3.
You can combine the two approaches. For a rectangle whose edges are calculated at run time, give Range two Cells endpoints. Both endpoints should belong to the same worksheet, as they do here:
Sub FormatDataBlock()
Dim ws As Worksheet
Dim lastRow As Long
Dim dataBlock As Range
'Find the last nonblank cell in column A.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'Format rows 2 through the last data row, columns A through C.
If lastRow >= 2 Then
Set dataBlock = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 3))
dataBlock.Font.Bold = True
End If
End Sub
The End(xlUp) pattern starts at the bottom of column A and moves upward to the next occupied cell, much like pressing Ctrl+Up from the bottom of a column. On Windows, Ctrl+Up is the keyboard equivalent for moving through a region; on Mac, the corresponding navigation shortcut is usually Command+Up. In VBA, however, xlUp is the constant used in the code, independent of that keyboard shortcut.
Fixed and changing ranges
A fixed range is appropriate when the workbook layout is deliberately stable. If an input form always uses B2:B15, a direct reference is straightforward and easy to maintain. The drawback is that new rows added below row 15 will not be included unless the code is updated.
For lists that grow, calculate the final row instead of hard-coding it. One common technique is to find the last occupied cell in a column that is expected to contain an entry for every record. The example above uses column A. If column A can contain blanks while other columns still have data, it may stop too early; choose a dependable key column or use a different method suited to the sheet’s structure. If the column has no data, the technique returns row 1, which is why the example checks that the result is at least 2 before formatting data rows.
Other options have trade-offs. CurrentRegion expands to the contiguous block around a starting cell, but a completely blank row or column can split the block. A table can be a better fit for a regularly expanding dataset because its data area grows as records are added; VBA can refer to a table’s DataBodyRange. That property is Nothing when the table has no data rows, so code should account for an empty table before operating on it.
Reading, writing, and changing cells
Once a range is identified, VBA can interact with its properties and methods. A single cell’s Value can be read into a variable or assigned a new value. For a block of same-sized cells, assigning one range’s Value to another copies the cell values without requiring a cell-by-cell loop. Make sure source and destination dimensions match when copying a multi-cell block this way.
Sub CopyValuesOnly()
Dim ws As Worksheet
Dim sourceRange As Range
Dim targetRange As Range
'The source and destination are both three rows by two columns.
Set ws = ThisWorkbook.Worksheets("Data")
Set sourceRange = ws.Range("A2:B4")
Set targetRange = ws.Range("D2:E4")
targetRange.Value = sourceRange.Value
End Sub
This copies values, not formulas, formatting, comments, or column widths. For a full copy that includes formatting, VBA provides the range’s Copy method, but it uses Excel’s copy behavior and may involve the clipboard. Choose the operation based on what should be preserved rather than assuming that every kind of copy is equivalent.
Be precise about clearing. ClearContents removes values and formulas but keeps formatting. ClearFormats removes formatting while leaving cell contents. Clear removes contents and formatting, so it is broader and can disrupt a carefully designed template. Similarly, assigning a blank value is not the same as deleting cells and shifting neighboring data.
Multi-cell and noncontiguous ranges
A range address can describe a rectangular block, but some tasks require separate areas. VBA can represent multiple areas with a comma-separated address inside a range expression, such as ws.Range("A1:A5,C1:C5"). This is useful when the same action should affect two distinct columns without including the cells between them. Operations that expect a single rectangular block may not behave as expected on a multi-area range, so check the operation’s behavior before applying it.
For a range relative to a known starting cell, Offset and Resize can make code more adaptable. Offset moves the reference by a number of rows and columns; Resize changes its dimensions. Both are useful for building references from a starting point, but an incorrect row or column count can place the resulting range outside the intended data. A small test on a copy of the workbook can help confirm the address before a macro makes broad changes.
Common range errors and how to avoid them
Many range bugs come from the workbook context rather than the address itself. A macro may run while another sheet or workbook is active, or the code may use an unqualified range. Naming the workbook and worksheet where appropriate makes the target explicit. Also check whether a sheet name contains a space; VBA still uses the exact tab name inside quotation marks.
- Wrong sheet: Qualify ranges with a worksheet variable, for example
ws.Range("A1"), rather than relying on the active sheet. - Off-by-one last row: Decide whether the header row belongs in the operation and check for an empty data column before using a calculated row.
- Unexpected blanks: A last-row calculation based on one column can miss records when that column has gaps.
- Range size mismatch: When assigning one multi-cell range’s values to another, confirm that both have the same number of rows and columns.
- Wrong clearing method: Use
ClearContentswhen formatting should remain; use broader clearing only when that is genuinely intended.
Another common misconception is that UsedRange always means “the cells with current data.” It describes the worksheet’s used area as Excel recognizes it, and formatting or prior edits can make that area larger than the visible dataset. It is not always a reliable substitute for a deliberate last-row rule. Likewise, selecting a range is usually unnecessary: code can change a qualified range directly without activating a sheet or selecting cells first.
Running macros and protecting the workbook
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On Mac, the shortcut is commonly Option+F11, sometimes with the Fn key depending on keyboard settings. The Developer tab may need to be shown: in Windows, use File > Options > Customize Ribbon and select Developer. On Mac, use Excel > Settings > Ribbon & Toolbar and enable Developer. Excel for the web does not run VBA macros; use the desktop application for VBA.
Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving as a standard .xlsx file does not retain VBA code. Only enable macros in files from sources you trust. On Windows, macro controls are available through File > Options > Trust Center > Trust Center Settings > Macro Settings. Avoid enabling all macros as a routine workaround. If a workbook is blocked or prompts unexpectedly, verify where it came from and follow your organization’s security guidance rather than bypassing protections.
Before running code that edits a range, review the worksheet name and address, and save a backup copy. Macros cannot be undone with Ctrl+Z, and a macro that clears or overwrites cells may change many values in one operation. For a first run, use a duplicate workbook with representative sample data and inspect the affected cells afterward.
Frequently Asked Questions
How do I select a range in an Excel macro? Use a worksheet-qualified reference such as ThisWorkbook.Worksheets("Data").Range("A1:B10").Select if selection is specifically necessary. Most macros do not need to select cells; assigning the range to a variable and working with it directly is generally less dependent on the active sheet.
How do I refer to a whole column or row? On a worksheet variable named ws, use ws.Columns("A") for column A or ws.Rows(1) for row 1. A whole-column or whole-row operation can affect many cells, so use a smaller range when only part of the sheet should change.
What is the difference between Range(“A1”) and Cells(1, 1)? Both identify cell A1 when they refer to the same worksheet. Range("A1") uses an address written in A1 notation; Cells(1, 1) uses row and column numbers, which can be easier to calculate in loops or dynamic code.
Can an Excel macro use a named range? Yes. A worksheet-qualified reference such as ws.Range("InputArea") can address a named range, provided the name is valid in that workbook context. Check whether the name refers to the intended cells and whether it is workbook-scoped or worksheet-scoped.
Can Excel for the web run a VBA range macro? No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA. Open the workbook in the desktop Excel app to execute VBA; Office Scripts are a separate automation option and are not VBA macros.
Related
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Not Equal: Operators, Examples, and FixesIn Excel VBA, use the <> operator to test whether two values are not equal. For example, If…
- VBA Macro to Copy and Paste a Row Below the Last RowTo copy the last data row into the row immediately below it, find the last occupied cell in…
- Excel VBA: Open a Workbook Without Updating LinksTo open an Excel workbook with VBA and skip updating its external workbook links, pass UpdateLinks:=0 to Workbooks.Open.…
- Convert an Excel Range to HTML in VBATo convert a range to a formatted HTML file in Excel for Microsoft 365 on Windows, add a…
- VBA Sort Sheets Alphabetically: Macro and StepsYes. In Excel for Microsoft 365 on Windows, a short VBA macro can sort a workbook’s sheet tabs…
- Excel VBA UserForm Examples: Forms, Code, and Setup GuideCommon Excel VBA UserForm examples include a contact-entry form, a search box, a data-editing dialog, and a task-specific…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.