Cell() syntax for multi-cell ranges

[John Kauffman]

Can someone help with the syntax for Cell() for a multi-cell range?

Range(A1) is expressed as Cells(1,1)
Range(A1:B2) is expressed how in Cells()?

Thanks

 

[hiker95]

John Kauffman,


Range(A1:B2) is expressed how in Cells()?


Try:
Range(Cells(1,1),Cells(2,2))

 

[Michael M]

Try

Range(Cells(1,1),Cells(2,2))

 

[mirabeau]

or

Cells(1).Resize(2, 2)

or

Range(Cells(1), Cells(Columns.Count + 2))

really depends on how you want to subsequently use it

 

[John Kauffman]

Hiker; Thanks for the help. Range(Cell(1,1),Cell(2,2)) works fine.

I was hoping there would be a solution with just Cell(). The RC values are hard-coded (not variables) so it works to use Range() by itself.

I wanted to use Cell() instead of Range() for two reasons.
- The surrounding lines of code I'm using Cell() and I thought consistency would be good
- I never had reason to think about it before and want to know if possible.

If anyone knows a technique to ref a multi-cell range with just Cell(), I'm all ears.

Much Thanks.

 

[mirabeau]

if you like you can try these sorts of code

Sub xxx()
Dim cell(0)
Set cell(0) = cells.Resize(3, 5).Offset(4, 2)

cell(0).Select

End Sub

or

Sub yyy()
cells().Resize(2, 4).Select
End Sub

or similar.

But ???

 

[Jerry Sullivan]

I was hoping there would be a solution with just Cell(). The RC values are hard-coded (not variables) so it works to use Range() by itself.

I wanted to use Cell() instead of Range() for two reasons.
- The surrounding lines of code I'm using Cell() and I thought consistency would be good
- I never had reason to think about it before and want to know if possible.

Hi John,

If the cell reference is hard-coded as you say it is, the simplicity and clarity of a reference like Range("A1:B2") or [A1:B2] is hard to improve upon.

If you are wanting to be more consistent with the surrounding code, you might consider using Alpha references for the columns in that surrounding code IE:
Cells(1,"A")

 

[John Kauffman]

Thanks to all, you provided some great ideas.

It seems Cell() has no strict analogy to Range("A1:B2") like Cell(1,1:2,2). That satisfied my curiosity.

You have provided several ways to do the same job, and that satisfied my need.

Much thanks.