Getting a subrange of a specified range.

[y]

How can I get a subrange of a range?
Or simply a row or a column?

I'm looking for a trick like this

Supposing rng1= $A$3:$G$56

rng2 will be equal to $B$4:$D$7

Thanks Alex.

 

[Frank Kabel]

Hi
try
=rng1.offset(1,1).resize(4,3)

 

[Alan Beban]

Frank Kabel wrote:
Or = Range(rng1(2, 2), rng1(5, 4))

Not sure what your 2,2,4,3 is supposed to represent.

Alan Beban

 

[Frank Kabel]

Hi Alan
i was also gueesing but as the OP did not respond either i hit the
point or he did not read it

 

[Harlan Grove]

Or = Range(rng1(2, 2), rng1(5, 4))

Not sure what your 2,2,4,3 is supposed to represent. ....
Frank Kabel wrote: ....

Given rng1 and the intended rng2, one guess for 2,2,4,3 would be something
like =OFFSET(INDEX(rng1,2,2),0,0,4,3), which suggests

Set rng2 = rng1.Cells(2, 2).Resize(4, 3)

which could be implemented as


Function GetSubRange(rng1 As Range, ir As Long, ic As Long, _
nr As Long, nc As Long) As Range

On Error Resume Next
Set GetSubRange = rng1.Cells(ir, ic).Resize(nr, nc)

End Function


which would preserve the OP's argument semantics.