Is the . in .Range necessary when defined by .Cells?

The Problem (Q-score 7, ranked #130th of 303 in the Excel VBA archive)

The scenario as originally posted in 2016

It is pretty much widely accepted that this is not ‘best practise’.

dim rng as range
with thisworkbook    '<~~ possibly set an external workbook 
    with .worksheets("sheet1")
        set rng = .range(cells(2, 1), cells(rows.count, 1).end(xlup))
    end with
end with

The two Range.Cells properties that define the scope of the Range object will default to the ActiveSheet property. If this is not Sheet1 (defined as the .Parent in the With … End With statement), the assignment will fail with,

Run-tim error '1004': Application-defined or object-defined error

Solution: use .Cells not Cells. Case closed.

But…

Is the . necessary in this Range object definition when both the Range.Cells properties inherit the .Parent worksheet property that is defined in the With … End With statement?

How can this,

dim rng as range
with thisworkbook    '<~~ possibly set an external workbook 
    with .worksheets("sheet1")
        ' define rng as Sheet1!A2 to the last populated cell in Sheet1!A:A
        set rng = .range(.cells(2, 1), .cells(rows.count, 1).end(xlup))  '<~~ .range
    end with
end with
debug.print rng.address(0, 0, external:=true)

… be different from this,

dim rng as range
with thisworkbook    '<~~ possibly set an external workbook 
    with .worksheets("sheet1")
        ' define rng as Sheet1!A2 to the last populated cell in Sheet1!A:A
        set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup))  '<~~ range not .range
    end with
end with
debug.print rng.address(0, 0, external:=true)

We use .range when the parameters that define the scope of the range are ambiguous; e.g. .range([A1]) The A1 cell could be from any worksheet and will default to the ActiveSheet property without the .. But why do we need to reference the parent of a range object when the scope that defines it has properly referenced its parent worksheet?

Why this Range / Worksheet targeting trips people up

The question centers on reaching a specific cell, range, or workbook object. In Excel VBA, this is the #1 source of failures after activation events: every property (.Value, .Formula, .Address) behaves differently depending on whether the parent Workbook is explicit or implicit.

The Verified Solution — solid answer (above median) (+11)

3-line Excel VBA pattern (copy-ready)

My opinion is slightly different here.

YES it is required. You can’t always control where the user may run the code from.

Please consider these few test cases

SCENARIO

Workbook has 2 worksheets. Sheet1 and Sheet2

TEST 1 (Running from a module)

Both Code give same result

TEST 2 (Running from a Sheet code area of Sheet1)

Both Code give same result

TEST 3 (Running from a Sheet code area of Sheet2)

'~~> This code fails
set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup))

You will get Application Defined or Object defined error

And hence it is always advisable to properly qualify your objects so that the code can run from anywhere

When to Use It — classic (2013–2016)

Ranked #130th in its category — specialized fit

This pattern sits in the 97% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.

What changed between 2016 and 2026

The answer is 10 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.