Is there a way to address a range by row # and column #?

[Khaki]

sorry for the newbie question, but is there a way to address a range b
row # and column #?

Eg instead of range("B5"), I would somehow address it b
funcitionX(2,5) where 2 is B and 5 is column 5. I think I am askin
about r1c1 notation?

TIA

 

[Frank Kabel]

Hi
try
cells(5,2)
instead of
range("B5")

 

[Khaki]

guten morgna frank

another question, how would i do a range using cellls()?

I tried something like this, but it does not work
Range(cells(5,2) + "," + cells(7,7))


TIA!

 

[Frank Kabel]

Hi
try
Range(cells(5,2),cells(7,7))

Should give you the range B5:G7

 

[Dave Peterson]

Just to add to Frank's reply.

If you're going to be working with ranges, you might want to get in the habit of
fully qualifying them.

Unqualified ranges usually refer to the activesheet--but sometimes you need to
get a range from a worksheet that isn't active.

Instead of
range("a1") or cells(1,1)
you could use
worksheets("sheet1").range("a1")
or
activesheet.cells(1,1)

And for Frank's suggestion, I'd use something like this:

dim myRng as Range
with worksheets("sheet1")
set myrng = .range(.cells(5,2),.cells(7,7))
end with

The leading dots mean that object is contained in the previous With's object (in
this case worksheets("sheet1").

It might save lots of wear and tear when you're tying to debug your code.

 

[Frank Kabel]

Just to add to Frank's reply.

If you're going to be working with ranges, you might want to get in
the habit of fully qualifying them.

Dave
I totally agree with you on this subject
Frank

 

[Robert McCurdy]

And for further variations.

Cells(5, 2).Resize(3, 6)

Will also return B5:G7

Regards Robert

 

[Khaki]

Thanks for all for enlighting me!

Very much appreciated!