The Range Object: Excel's Central Workhorse
Almost every meaningful Excel macro touches a Range object, which represents one cell, a block of cells, a whole row or column, or even a non-contiguous selection. You most often obtain one with the Range property — Range("A1"), Range("A1:C10"), or Range("A1,C3") for disjoint areas — always relative to a specific worksheet. A single-cell range like Range("A1") still has the full set of Range members, so there is no separate 'Cell' object in VBA. Because Range is so central, understanding how to reference it precisely and read its .Value is the single highest-leverage skill in Excel automation.
Cricket analogy: The Range is the bat you use for nearly every shot — whether defending a single or hitting a six, it's the same tool, just as Range handles one cell or a whole block.
Range versus Cells: Two Ways to Address the Same Grid
The Cells property addresses a cell by numeric row and column — Cells(2, 3) means row 2, column 3 (i.e. C2). This numeric form is invaluable inside loops because you can drive the indices with variables: Cells(i, j). By contrast Range uses A1-style string addresses, which read more naturally for fixed locations. The two combine powerfully: ws.Range(Cells(1, 1), Cells(10, 3)) builds the block A1:C10 from numeric corners. Remember that Cells with no arguments, Cells, refers to every cell on the sheet, and that column numbers start at 1 for column A.
Cricket analogy: Cells(row, col) is like a fielding position given by coordinates on the ground, while Range("A1") is calling it 'silly mid-off' — coordinates suit a computer plotting positions in a loop.
Sub RangeVsCells()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' A1-style, natural for fixed spots
ws.Range("A1").Value = "Product"
' Numeric, ideal for loops
Dim i As Long
For i = 2 To 6
ws.Cells(i, 1).Value = "Item " & (i - 1)
ws.Cells(i, 2).Value = i * 10 ' price
Next i
' Build a block from numeric corners
ws.Range(ws.Cells(1, 1), ws.Cells(6, 2)).Interior.Color = RGB(230, 240, 255)
End SubWhen combining Range with Cells across sheets, always qualify BOTH Cells calls with the same worksheet: ws.Range(ws.Cells(1,1), ws.Cells(6,2)). If you leave a Cells unqualified, it silently refers to the ActiveSheet and can raise a 1004 error or read the wrong sheet.
Reading and Writing Efficiently with Arrays
Touching cells one at a time is the classic performance killer in VBA because each read or write crosses the boundary between VBA and Excel's calculation engine. For large blocks, read the whole range into a Variant array in a single statement — arr = ws.Range("A1:C1000").Value — process the array in memory, then write it back with ws.Range("A1:C1000").Value = arr in one shot. This can turn a multi-second loop into milliseconds. The resulting array is two-dimensional and, importantly, its indices start at 1 (not 0), matching Excel's row and column numbering.
Cricket analogy: Reading cells one by one is like running a single every ball; loading a range into an array is clearing the boundary rope in one stroke — far more runs per unit of effort.
Do not use .Select or Selection to read and write cells in production code — it is slow and depends on which sheet is active. Also beware that reading .Value returns formatted-independent underlying values, while .Text returns the displayed string; use .Value2 to avoid Date/Currency type conversions when you only need the raw number.
Navigating Ranges: Offset, Resize, End, and CurrentRegion
Several Range properties let you move and reshape a reference relative to a starting point. Offset(rows, cols) returns a range shifted from the original — Range("A1").Offset(0, 1) is B1. Resize(rows, cols) changes the block's dimensions from its top-left anchor. The End property mimics Ctrl+Arrow: Range("A1").End(xlDown) jumps to the last cell in a contiguous run, which is the standard way to find the last used row. CurrentRegion returns the whole contiguous block of data surrounding a cell, bounded by blank rows and columns — perfect for grabbing a table without knowing its exact size.
Cricket analogy: Offset is nudging a fielder two steps squarer from their current spot; End(xlDown) is the ball racing to the boundary until it hits the rope, finding the edge of the field in one motion.
Sub NavigateRanges()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last used row in column A
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Debug.Print "Last row: " & lastRow
' Grab the whole contiguous table around A1
Dim tbl As Range
Set tbl = ws.Range("A1").CurrentRegion
Debug.Print "Table has " & tbl.Rows.Count & " rows"
' Write a header one column to the right of A1
ws.Range("A1").Offset(0, tbl.Columns.Count).Value = "Notes"
End Sub- The Range object represents one cell, a block, entire rows/columns, or non-contiguous areas; there is no separate Cell object.
- Range uses A1-style strings; Cells(row, col) uses numeric indices ideal for loops, with column A being column 1.
- Combine them as Range(Cells(...), Cells(...)) but qualify every Cells call with the same worksheet.
- For speed, read a block into a Variant array, process in memory, and write it back in a single assignment; array indices start at 1.
- Avoid .Select/Selection; prefer .Value, and use .Value2 to skip Date/Currency conversions.
- Offset shifts a reference; Resize reshapes it; End(xlUp)/End(xlDown) finds edges of contiguous data.
- CurrentRegion returns the whole contiguous data block bounded by blank rows and columns.
Practice what you learned
1. What does Cells(3, 2) refer to?
2. Why is reading a large range into a Variant array faster than looping cell by cell?
3. What is the standard idiom to find the last used row in column A?
4. What does CurrentRegion return?
5. When combining Range with Cells for a block on a specific sheet, what must you do?
Was this page helpful?
You May Also Like
The Workbook and Worksheet Objects
Learn how the Workbook and Worksheet objects sit at the heart of Excel's object model, and how to reference, add, activate, and manage them reliably in VBA.
Working with Charts in VBA
Create, configure, and format Excel charts programmatically using the ChartObject and Chart objects, set source data and chart types, and understand embedded charts versus chart sheets.
Events in Excel VBA
Understand Excel's event-driven model — workbook, worksheet, and application events — and how to write handlers that respond automatically to user actions like edits, selections, and file opens.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics