CODEWITHSUNDEEP
vbaexcel
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

Can you refer to an object within its With block?

In a "With" block, is there a way to refer to the subject of the block itself?

For instance, if I wanted to copy text into a cell from the cell below it, I could say:

With MyRange    
    .cells(1).offset(1,0).copy .cells(1)
End With

But could I also do this?

With MyRange.cells(1)   
    .offset(1,0).copy [???]
End With

The "[???]" would be some simple way of saying "MyRange.Cells(1)".

Upvotes: 1

Views: 175

Answers (2)

David Zemens
David Zemens

Reputation: 53623

.Cells would work, as the Cells property will return the range itself.

With MyRange.cells(1)   
    .offset(1,0).copy .Cells
End With

The reasoning is this: .Cells property returns a range object, and you can use the .Cells against that range object, and further this extends to any property which returns a Range, such as:

With Sheet1.ListObjects(1).ListColumns(1).DataBodyRange
    Debug.Print .Cells.Address  '#returns the address of entire "self" range in specified column's DataBodyRange
End With

The .DataBodyRange property of a ListObject returns a Range. So does the ListColumns, HeaderRowRange, etc. Any property of any object which represents a range, returns a range. These other objects are not a class of their own, but a property of another object, which return a Range.

This accommodates single or multi-cell ranges. And of course, the .Cells can be fine-tuned if you need to manipulate individual cells, like: .Cells(1), etc.

Upvotes: 4

Scott Craner
Scott Craner

Reputation: 152485

You can use the Offset(0) to refer to itself:

With MyRange.cells(1)   
    .Offset(1).Copy .Offset(0)
End With

Upvotes: 2

Related Questions