referencing ranges using R1C1 format

[pwermuth]

Hello,

this might be a very basic problem but I cannot find the solution to it
in Excel Help.

There are many instances when I need to use Range instead of Cell to
refer to a cell. For example methods such as ClearContents only work
on Range not on Cell.
However, it seems as if Range only takes the A1 format which drives me
crazy. Most of the time I am looping through rows and columns and
therefore will have their integer values, i.e. the location of a cell
or array in R1C1 format but not in the A1 format.

How can I refer to cell R5C3 for example using Range? Supposedly there
is a second syntax for Range which should work as follows
Range(Cells(5,3),Cells(5,3)) but whenever I try to use that I get
runtime errors. Am I getting this wrong and is there another, better
method to quickly convert a simply R1C1 format address into A1 format
or to use Range with the R1C1 format?

Thanks for your help,

Peter

 

[Gary Keramidas]

this clears a1 in excel 2k3

Cells(1, 1).ClearContents

this clears a1:b2

Range(Cells(1, 1), Cells(2, 2)).ClearContents

 

[pwermuth]

Thank you, but the

Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get
runtime errors. Are there some limitations I need to be aware of? For
example, can I not refer to a single cell (i.e. Range(Cells(2,2),
Cells(2,2)) ) using this syntax?

 

[Dave Peterson]

The only trouble I've had with that syntax is when I don't qualify the ranges.

I like:

dim rng as range
with activesheet 'worksheets("sheet1")
set rng = .range(.cells(1,1),.cells(2,2))
end with

And I could refer to a single cell using your example--but I wouldn't.

 

[Alan Beban]

The only trouble I've had with that syntax is when I don't qualify the ranges.

One can avoid the qualification problem with

Set baseRng = ActiveSheet.Range("A1")
Set rng=Range(baseRng(1,1),baseRng(2,2))

You can refer to a single cell with, e.g., baseRng(2,2)

Alan Beban

I like:

dim rng as range
with activesheet 'worksheets("sheet1")
set rng = .range(.cells(1,1),.cells(2,2))
end with

And I could refer to a single cell using your example--but I wouldn't.

 

[Dave Peterson]

There are even potential problems with this--depending on where the code is:

I put this in the module for Sheet1:

Option Explicit
Sub testme()
Dim baseRng As Range
Dim rng As Range
Set baseRng = Worksheets("sheet2").Range("A1")
Set rng = Range(baseRng(1, 1), baseRng(2, 2))
End Sub

I got an error with that last "set rng = Range(baseRng(1, 1), baseRng(2, 2))"
statement.

If I changed it to:
Set rng = Application.Range(baseRng(1, 1), baseRng(2, 2))

It worked fine.

I bet it's because that unqualified range() is still looking at the sheet that
owns the code.

The only trouble I've had with that syntax is when I don't qualify the ranges.

One can avoid the qualification problem with

Set baseRng = ActiveSheet.Range("A1")
Set rng=Range(baseRng(1,1),baseRng(2,2))

You can refer to a single cell with, e.g., baseRng(2,2)

Alan Beban

 

[Alan Beban]

That's what it seems; the following works in the module for Sheet1:

Sub testme3()
Dim baseRng As Range
Dim rng As Range
Set baseRng = Worksheets("Sheet2").Range("A1")
Set rng = baseRng(2, 2)
MsgBox rng.Parent.Name & " " & rng.Address '<---Displays Sheet2 $B$2
End Sub

Alan Beban