Getting a subrange of a specified range.
 

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=GetSubRng(rng1,2,2,4,3) <<

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

Thanks Alex.

Getting a subrange of a specified range.
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"y" schrieb im Newsbeitrag
...
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=GetSubRng(rng1,2,2,4,3) <<

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

Thanks Alex.

Getting a subrange of a specified range.
 

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
Hi
try
=rng1.offset(1,1).resize(4,3)

--
Regards
Frank Kabel
Frankfurt, Germany

"y" schrieb im Newsbeitrag
...

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=GetSubRng(rng1,2,2,4,3) <<

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

Thanks Alex.

Getting a subrange of a specified range.
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Alan Beban" schrieb im Newsbeitrag
...
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
Hi
try
=rng1.offset(1,1).resize(4,3)

--
Regards
Frank Kabel
Frankfurt, Germany

"y" schrieb im Newsbeitrag
...

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=GetSubRng(rng1,2,2,4,3) <<

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

Thanks Alex.

Getting a subrange of a specified range.
 

"Alan Beban" wrote...
Or = Range(rng1(2, 2), rng1(5, 4))

Not sure what your 2,2,4,3 is supposed to represent.
....
Frank Kabel wrote:
....
=rng1.offset(1,1).resize(4,3)
....
"y" schrieb im Newsbeitrag
....
I'm looking for a trick like this

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

rng2=GetSubRng(rng1,2,2,4,3)

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

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.