Reduce size of Range - Syntax question

[parsellj]

I hope this is a trivial question!
Given a Worksheet variable - WS,
and a Single Column Range on that Worksheet - V (eg G1:G260),
and an Index -- i where i is the index of the first row of interest in that range (eg 12),
What is the VB or VBA syntax for setting V to be the Range starting with G12 and ending at G260.

I am looking for the "proper" idomatic way to do this and am having a mental block.
TIA,
Jim

 

[Norie]

Jim

There are different ways to do this, none of which I would say were the 'proper' way.

Here's one.

Set rng = Range("G1:G260")

idx = 12

Set rngNew = Rng.Offset(idx - 1).Resize(Rng.Rows.Count - idx + 1)

Msgbox rngNew.Address

Here's another one.

Set rng = Range("G1:G260")

idx = 12

Set rngNew = Range(rng.Cells(idx, 1), rng.Cells(rng.Rows.Count, 1))

MsgBox rngNew.Address

 

[Rick Rothstein]

Jim

There are different ways to do this, none of which I would say were the 'proper' way.

Here's one.

Set rng = Range("G1:G260")

idx = 12

Set rngNew = Rng.Offset(idx - 1).Resize(Rng.Rows.Count - idx + 1)

Msgbox rngNew.Address

Here's another one.

Set rng = Range("G1:G260")

idx = 12

Set rngNew = Range(rng.Cells(idx, 1), rng.Cells(rng.Rows.Count, 1))

MsgBox rngNew.Address

And here is another one...

Set rng = Range("G1:G260")

idx = 12

Set rngNew = Intersect(rng, rng.Offset(idx - 1))

MsgBox rngNew.Address

 

[parsellj]

Thanks for both of your answers. I was using a "slightly" mangled version of the 2nd approach - a mental block somehow had me using Cells rather than Range as the first term after the = sign - bad (overflow) result! This was a bit more awkward since the code actually lives in a VSTO solution.
Thanks for helping me over that one and for two other ways of getting the desired results! Actually I like both #1 and #3 better than #2!