Getting a subrange of a specified range.

Y

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.
 
A

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
 
F

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 :)
 
H

Harlan Grove

Alan Beban said:
Or = Range(rng1(2, 2), rng1(5, 4))

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

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.
 
Top