No simple way to refer the object we are dealing 'With' while dealing with?

[prabhafriend]

With Cells.SpecialCells(xlCellTypeLastCell)

Range(.Column.Cells(2), Cells.SpecialCells(xlCellTypeLastCell)).Value = "Blah Blah"
End With

Do we have to refer it all the way down? No simple way?

[Bob Phillips]

With Cells.SpecialCells(xlCellTypeLastCell)

Set rng = Range(.Column.Cells(2), Cells.SpecialCells(xlCellTypeLastCell))

End With

rng.Value = "Blah Blah"

[prabhafriend]

Mr.xld. The actual question I asked is why we have to type this thing again "Cells.SpecialCells(xlCellTypeLastCell)" despite we already typed it along with the 'With' Keyword itself. It will be better if our members put some thoughts on user's motives before answering.

[Bob Phillips]

Well sorry about that. Your explanation was so good that it was stupid of me to fail miserably to read what you meant rather than what you said, and to interpret it the way I did.

Bin for you my friend.

[Paul_Hossler]

Option Explicit
Sub drv()
Dim rStart As Range, rLast As Range

'unrolling the With
'Range(Cells.SpecialCells(xlCellTypeLastCell).Column.Cells(2), Cells.SpecialCells(xlCellTypeLastCell)).Value = "Blah Blah"
' the .Column is invalid
'I suspect you meant .EntireColumn
Range(Cells.SpecialCells(xlCellTypeLastCell).EntireColumn.Cells(2), Cells.SpecialCells(xlCellTypeLastCell)).Value = "Blah Blah"

'using the With
With Cells.SpecialCells(xlCellTypeLastCell)
Range(.EntireColumn.Cells(2), .Cells).Value = "Blah Blah"
End With


'I'd opt for readabilty
Set rLast = Cells.SpecialCells(xlCellTypeLastCell)
Set rStart = rLast.EntireColumn.Cells(2)
Range(rStart, rLast).Value = "Blah Blah"
End Sub


Paul