VBA Range Properties: Values, Formatting, and Navigation

In Excel VBA, a Range property either returns information about cells or another range object, or changes a cell or range setting. The most useful examples include Value for cell contents, Address for a reference, Row and Column for position, and Font, Interior, and NumberFormat for formatting.

What a Range property does

A Range is an object that represents one cell, a rectangular block of cells, or a collection of separate areas. Its properties describe or control that object. Some properties return ordinary values: Range("A1").Value returns the cell’s value, while Range("A1").Row returns its row number. Other properties return objects that have properties of their own: Range("A1").Font returns a Font object, so you can then read or set its Bold, Color, or other formatting properties.

Properties are not the same as methods. A property commonly gets or sets a value, while a method performs an action. For example, Interior.Color sets a fill color, whereas ClearContents is a method that removes cell contents. Understanding the distinction helps explain why some expressions are chained, such as cell.Font.Bold = True, and others are called like actions, such as cell.ClearContents.

Many properties can be read, written, or both. Address is generally used to retrieve a reference; Value can retrieve or replace contents; and NumberFormat can retrieve or set the display format. The exact behavior depends on the property and the kind of range.

Referencing a range safely

A range reference should normally identify its worksheet explicitly. An unqualified expression such as Range("A1") refers to the active sheet, which can change when a user clicks another worksheet. Qualifying the reference makes the target clear:

Sub ShowQualifiedRange()
    Dim target As Range

    ' Point to A1 on the Data worksheet.
    Set target = ThisWorkbook.Worksheets("Data").Range("A1")
    Debug.Print target.Address
End Sub

Here, ThisWorkbook means the workbook containing the running VBA code. If the intended workbook is a different one, use a suitable workbook object instead. A worksheet’s Range property accepts references such as "A1", "B2:D10", or a named range. When a macro uses Cells, qualify that property with the worksheet too; otherwise it can also refer to the active sheet.

To open the Visual Basic Editor on Windows, use Alt+F11. On Mac, use Option+F11; some keyboards require Fn as well. Excel for the web does not run VBA macros, so use the desktop application for the code examples. Before running a macro that changes a workbook, save a separate copy: VBA changes cannot be undone with Ctrl+Z.

Value, Value2, Formula, and Text

Value is the usual property for reading or writing a cell’s contents. For example, setting Range("B2").Value = 25 places the number 25 in B2. It can return different Variant subtypes depending on the cell, including a number, text, a date-like value, or an Excel error value. A blank cell generally returns an empty value.

Value2 is similar, but it does not return VBA Date or Currency subtypes. Date and currency values are represented as numbers instead, so code that uses Value2 may need to interpret or format those numbers itself. It is often a practical choice for transferring ordinary cell data, especially in blocks, but choose it with awareness of how your code handles dates and currency.

Formula reads or writes a formula in A1-style notation. If B2 contains =SUM(B3:B8), then Range("B2").Formula returns that formula rather than its calculated result. In Microsoft 365, Formula2 supports the newer dynamic-array formula behavior; use it when your code specifically needs that behavior. Text, by contrast, returns the displayed text. It is read-only and can reflect number formatting or a narrow column—for example, a number might display as hash marks even though its underlying value is intact.

For a multi-cell range, Value can be assigned a two-dimensional array to populate cells in one operation. A range’s Formula can also be read or assigned across cells, but the result depends on the range and formula pattern. Do not treat Text as a reliable way to transfer underlying data: it is about display, not the stored value.

Position and reference properties

Row and Column return the worksheet row and column numbers of the range’s first cell. Thus, a range beginning at D7 has Row equal to 7 and Column equal to 4. For dimensions, use Rows.Count and Columns.Count: the range D7:F10 has four rows and three columns. Count reports the number of cells in a range; CountLarge is available when a very large range could exceed the capacity of the ordinary count.

Address returns a cell reference. By default, it uses absolute row and column markers, such as $D$7. Its optional arguments let code request relative references or a different reference style. A range can also be addressed through Cells, which uses row and column indexes relative to that range. For example, the cell at row 2, column 2 within B2:D5 is C3.

Sub InspectRangePosition()
    Dim block As Range

    ' The block has four rows and three columns.
    Set block = ThisWorkbook.Worksheets("Data").Range("D7:F10")
    Debug.Print block.Address, block.Row, block.Column
    Debug.Print block.Rows.Count, block.Columns.Count
End Sub

For a range containing multiple separate areas, such as a selection of nonadjacent cells, be careful: properties such as Row describe the first area’s first cell, not a list of every row represented. Use the Areas collection when code must inspect each separate section.

Moving and resizing a range

Offset returns a range shifted from the original by a number of rows and columns, while keeping its dimensions. Resize returns a range with a changed row or column count, anchored at the original range’s upper-left cell. Both are useful for loops and table-like work because they create a reference without selecting cells.

For instance, starting with B2, Offset(1, 0) refers to B3. Starting with B2:D5, Offset(1, 0) refers to B3:D6, still four rows by three columns. Resizing B2 to three rows and two columns gives B2:C4. The row and column arguments to these properties are counts or offsets, not worksheet coordinates.

End moves to the edge of a contiguous region in a direction such as xlDown or xlToRight; it corresponds broadly to using Ctrl with an arrow key in the worksheet. It can be useful for finding the end of a data block, but blank cells affect where it stops. A common pattern that starts at the bottom of a worksheet and moves upward is often more dependable for finding the last populated cell in a column than starting at the top and moving down, especially when gaps are possible.

Sub ReadNextCell()
    Dim startCell As Range
    Dim nextCell As Range

    ' Move one row down and keep the reference explicit.
    Set startCell = ThisWorkbook.Worksheets("Data").Range("B2")
    Set nextCell = startCell.Offset(1, 0)
    Debug.Print nextCell.Address
End Sub

Formatting and cell structure

Several properties expose formatting objects or settings. Font provides text formatting such as boldness and color; Interior provides fill properties; and NumberFormat controls how numbers are displayed. For example, a number can remain stored as 0.25 while a percentage number format displays it as 25%. Formatting changes how a value looks, not necessarily the value stored in the cell.

EntireRow and EntireColumn return the full worksheet rows or columns that intersect the range. These can be used to set properties such as Hidden, though hiding a row is different from deleting its contents. MergeCells indicates whether a range contains merged cells; when the range mixes merged and unmerged cells, the result may be Null. Merged cells can complicate sorting, filling, and range calculations, so avoid them in data tables when possible.

Sub FormatHeader()
    Dim header As Range

    ' Change formatting only; the cell values remain unchanged.
    Set header = ThisWorkbook.Worksheets("Data").Range("A1:C1")
    header.Font.Bold = True
    header.Interior.Color = RGB(217, 225, 242)
    header.NumberFormat = "General"
End Sub

In this example, RGB supplies a color value to the fill property. A range-level formatting assignment usually applies the setting to every cell in that range. If cells already have different formatting, reading a property across the entire range may not give a single useful value, so inspect individual cells or areas when the distinction matters.

Working with blocks, tables, and formulas

For contiguous data, CurrentRegion returns the block surrounding a range, bounded by blank rows and columns. It is convenient for a simple table with no completely blank row or column splitting the data. It is not a substitute for an Excel Table when the data structure needs stable headers and automatic expansion. Also, Excel does not allow CurrentRegion to be used on a protected worksheet.

HasFormula can help identify formula cells. For a range where every cell contains a formula it returns True; where none do, it returns False; and a mixture can produce Null. This three-state behavior matters: code that assumes the property is always simply true or false can mishandle a mixed range. The same general caution applies to some properties that summarize varied cells.

When a macro processes many cells, reading or writing a rectangular range in one assignment is generally simpler than repeatedly accessing individual cells. Arrays can hold the values returned from a multi-cell range, then be written back in a block. This also avoids unnecessary selection and reduces repeated interaction with the worksheet. Use Set when assigning an object reference such as a Range to a variable; ordinary values like a cell’s contents do not use Set.

Common mistakes and practical choices

Relying on the active sheet: qualify Range and Cells with a worksheet to prevent a click or another macro from redirecting the operation.
Confusing displayed text with stored data: use Value or Value2 for contents, Formula for formulas, and Text only when the displayed representation is what you need.
Assuming every property returns a simple value: Font, Interior, Rows, and Cells are objects or collections that can be followed by further properties.
Assuming a multi-cell range behaves like one cell: Row identifies the first cell’s row, while Rows.Count gives the range’s height. A mixed range can also yield Null for summary properties.
Selecting cells unnecessarily: direct references such as worksheet.Range("A1").Value are usually clearer than activating a sheet and selecting a cell before changing it.

For most data tasks, start with a fully qualified Range reference, then choose the property based on the question: Value2 for underlying data when its date and currency behavior suits the code, Formula or Formula2 for formulas, Address for identifying a reference, and formatting properties for appearance. Use Offset and Resize to build related ranges, and use CurrentRegion only when blank boundaries match the data layout.

Frequently Asked Questions

What is the default property of a VBA Range?
The default property is Value. In many contexts, Range("A1") used where a value is expected behaves like Range("A1").Value. Writing .Value explicitly is usually clearer, particularly for readers maintaining the code.

What is the difference between Value and Value2?
Both retrieve or assign cell contents. Value can represent dates and currency using VBA-specific subtypes, while Value2 does not; those values are represented numerically. Choose according to how the code needs to handle those data types.

How do I get the number of rows or columns in a range?
Use someRange.Rows.Count for its row count and someRange.Columns.Count for its column count. These count the dimensions of the referenced range, rather than returning its starting worksheet row or column.

Does Range.Text return the actual cell value?
No. It returns the text as displayed, which can be affected by number formatting and column width. Use Value or Value2 when the underlying cell content is needed.

Can VBA Range properties be used in Excel for the web?
VBA code runs in desktop Excel, not Excel for the web. A workbook containing macros can be stored online, but use the Windows or Mac desktop application to run its 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.