Using R1C1 in VB to select a range

[Guest]

I've seen a lot of posting about using the R1C1 format to select cells when
using VB to program a function into a cell, but not a lot about how to use
the R1C1 format to select a range for use in a VB Macro.

If I am in cell CA91 and I want to select the range starting at CA92 through
CE176, how to I program that into VB? I tried "range(rc1:r4c84).select" and
it doesn't like the colon. I then tried "range(rc[1]:r[4]c[84]).select" and
it bombed on the first open bracket ([).

Any ideas? I can't use the direct addresses since I won't always know where
I'm going to start this macro from.

 

[Guest]

Robert,
You might find this easier:

Sub GetRange()
Dim rng As Range
Set rng = Range("CA92").Resize(85, 5)
rng.Select
End Sub

Or .... where Activecell=CA91 to use your example

Sub GetRange()
Dim rng As Range
Set rng = ActiveCell.Offset(1, 0).Resize(85, 5)
rng.Select
End Sub

Avoid using select as much as possible as it slows performance. I used it to
illustrate that the range was correct.

HTH

 

[john]

if you want Range("R1C1:R4C84") to be selected, you can use the following
code:
1. Range("A1:CE4").select
or
2. Range(Cells(1, 1), Cells(4, 84)).select

Best Regards
John Black