Selecting a sub range?
 

If I have a range object passed to a function, eg, rng, does anybody know
how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

James

In the code below the column selected is E. The 2nd column in the range
D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody know
how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

ah so is that like saying
range nows equals just column

i.e. equivalent

rng = rng.Columns(2).Select (in essence really)

PS why is the "set" needed out of interest (I'm new to VBA if it wasnt
obvious :-), why not just say "rng = Range("D1:G100")" )

"Nick Hodge" wrote in message
...
James

In the code below the column selected is E. The 2nd column in the range
D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody know
how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

James

Imagine a Range is now like a stand alone Excel grid. It's columns are
numbered 1 (The far left column), 2 the one next and so on.

I have dimensioned the variable rng as a Range object (Set aside memory
space for it), Any object variable in VBA has to be 'set' before use. You
can then refer to all the methods and properties of the object by just using
rng instead of Worksheet("Sheet1").Range("A1"), so it's like shorthand.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
ah so is that like saying
range nows equals just column

i.e. equivalent

rng = rng.Columns(2).Select (in essence really)

PS why is the "set" needed out of interest (I'm new to VBA if it wasnt
obvious :-), why not just say "rng = Range("D1:G100")" )

"Nick Hodge" wrote in message
...
James

In the code below the column selected is E. The 2nd column in the range
D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody
know how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

Ah right so any object has to be "set" before use.

but the following integer statement doesnt need a set because its not an
object right?
Dim x As integer
x = 10 (NO SET?!)

ALSO
Just to clairfy after the statement
rng.Columns(2).Select

if i refer to rng, then for the remaining part of the function will I be
refering to column 2 when ever i refer to rng. i.e Is "select" basically
updating the accessible range part of the range object?

(sorry to be a pain but need to be sure what is happening so i can be sure
my function will work as expected)
Thanks
James

"Nick Hodge" wrote in message
...
James

Imagine a Range is now like a stand alone Excel grid. It's columns are
numbered 1 (The far left column), 2 the one next and so on.

I have dimensioned the variable rng as a Range object (Set aside memory
space for it), Any object variable in VBA has to be 'set' before use. You
can then refer to all the methods and properties of the object by just
using rng instead of Worksheet("Sheet1").Range("A1"), so it's like
shorthand.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
ah so is that like saying
range nows equals just column

i.e. equivalent

rng = rng.Columns(2).Select (in essence really)

PS why is the "set" needed out of interest (I'm new to VBA if it wasnt
obvious :-), why not just say "rng = Range("D1:G100")" )

"Nick Hodge" wrote in message
...
James

In the code below the column selected is E. The 2nd column in the range
D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody
know how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

James

You are right about integer..no need to Set...just objects

rng once set, will always refer to you range not just the columns, *all* the
properties and methods of that range. Select is just a method of the range
object, by setting the range object variable to rng you can use rng. to
access any properties or methods

rng.Select
rng.Cells.Count
rng.Resize...

Etc

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
Ah right so any object has to be "set" before use.

but the following integer statement doesnt need a set because its not an
object right?
Dim x As integer
x = 10 (NO SET?!)

ALSO
Just to clairfy after the statement
rng.Columns(2).Select

if i refer to rng, then for the remaining part of the function will I be
refering to column 2 when ever i refer to rng. i.e Is "select" basically
updating the accessible range part of the range object?

(sorry to be a pain but need to be sure what is happening so i can be sure
my function will work as expected)
Thanks
James

"Nick Hodge" wrote in message
...
James

Imagine a Range is now like a stand alone Excel grid. It's columns are
numbered 1 (The far left column), 2 the one next and so on.

I have dimensioned the variable rng as a Range object (Set aside memory
space for it), Any object variable in VBA has to be 'set' before use. You
can then refer to all the methods and properties of the object by just
using rng instead of Worksheet("Sheet1").Range("A1"), so it's like
shorthand.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
ah so is that like saying
range nows equals just column

i.e. equivalent

rng = rng.Columns(2).Select (in essence really)

PS why is the "set" needed out of interest (I'm new to VBA if it wasnt
obvious :-), why not just say "rng = Range("D1:G100")" )

"Nick Hodge" wrote in message
...
James

In the code below the column selected is E. The 2nd column in the
range D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody
know how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

ah yes but perhaps havent made myself clear,

i understand rng refers to the full range object here

rng.Columns(2).Select

now here does it refer to just column 2 or the whole range still


so if i just want to cycle through column 2 of range do i say

for each cell in rng
xxx
xx
next cell


OR


for each cell in rng.Columns(2).select
xxx
xx
next cell


OR (equivalently)


Dim newRng as Range
newRng.select(2)

for each cell in newRng
xxx
xx
next cell


basically ""rng.Columns(2).Select"" doesnt really make a lot of sense to me
on its own without an = really

MANY THANKS
James

"Nick Hodge" wrote in message
...
James

You are right about integer..no need to Set...just objects

rng once set, will always refer to you range not just the columns, *all*
the properties and methods of that range. Select is just a method of the
range object, by setting the range object variable to rng you can use rng.
to access any properties or methods

rng.Select
rng.Cells.Count
rng.Resize...

Etc

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
Ah right so any object has to be "set" before use.

but the following integer statement doesnt need a set because its not an
object right?
Dim x As integer
x = 10 (NO SET?!)

ALSO
Just to clairfy after the statement
rng.Columns(2).Select

if i refer to rng, then for the remaining part of the function will I be
refering to column 2 when ever i refer to rng. i.e Is "select" basically
updating the accessible range part of the range object?

(sorry to be a pain but need to be sure what is happening so i can be
sure my function will work as expected)
Thanks
James

"Nick Hodge" wrote in message
...
James

Imagine a Range is now like a stand alone Excel grid. It's columns are
numbered 1 (The far left column), 2 the one next and so on.

I have dimensioned the variable rng as a Range object (Set aside memory
space for it), Any object variable in VBA has to be 'set' before use.
You can then refer to all the methods and properties of the object by
just using rng instead of Worksheet("Sheet1").Range("A1"), so it's like
shorthand.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
ah so is that like saying
range nows equals just column

i.e. equivalent

rng = rng.Columns(2).Select (in essence really)

PS why is the "set" needed out of interest (I'm new to VBA if it wasnt
obvious :-), why not just say "rng = Range("D1:G100")" )

"Nick Hodge" wrote in message
...
James

In the code below the column selected is E. The 2nd column in the
range D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody
know how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james

Selecting a sub range?
 

From the Immediate window you can try all of these out...

? activesheet.Range("B2:E10").columns(2).address
$C$2:$C$10

? activesheet.Range("B2:E10").Range("A1").address
$B$2

? activesheet.Range("B2:E10").Range("B1:B2").address
$C$2:$C$3



--
Tim Williams
Palo Alto, CA


"James Cornthwaite" wrote in message ...
ah yes but perhaps havent made myself clear,

i understand rng refers to the full range object here

rng.Columns(2).Select

now here does it refer to just column 2 or the whole range still


so if i just want to cycle through column 2 of range do i say

for each cell in rng
xxx
xx
next cell


OR


for each cell in rng.Columns(2).select
xxx
xx
next cell


OR (equivalently)


Dim newRng as Range
newRng.select(2)

for each cell in newRng
xxx
xx
next cell


basically ""rng.Columns(2).Select"" doesnt really make a lot of sense to me
on its own without an = really

MANY THANKS
James

"Nick Hodge" wrote in message
...
James

You are right about integer..no need to Set...just objects

rng once set, will always refer to you range not just the columns, *all*
the properties and methods of that range. Select is just a method of the
range object, by setting the range object variable to rng you can use rng.
to access any properties or methods

rng.Select
rng.Cells.Count
rng.Resize...

Etc

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
Ah right so any object has to be "set" before use.

but the following integer statement doesnt need a set because its not an
object right?
Dim x As integer
x = 10 (NO SET?!)

ALSO
Just to clairfy after the statement
rng.Columns(2).Select

if i refer to rng, then for the remaining part of the function will I be
refering to column 2 when ever i refer to rng. i.e Is "select" basically
updating the accessible range part of the range object?

(sorry to be a pain but need to be sure what is happening so i can be
sure my function will work as expected)
Thanks
James

"Nick Hodge" wrote in message
...
James

Imagine a Range is now like a stand alone Excel grid. It's columns are
numbered 1 (The far left column), 2 the one next and so on.

I have dimensioned the variable rng as a Range object (Set aside memory
space for it), Any object variable in VBA has to be 'set' before use.
You can then refer to all the methods and properties of the object by
just using rng instead of Worksheet("Sheet1").Range("A1"), so it's like
shorthand.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
ah so is that like saying
range nows equals just column

i.e. equivalent

rng = rng.Columns(2).Select (in essence really)

PS why is the "set" needed out of interest (I'm new to VBA if it wasnt
obvious :-), why not just say "rng = Range("D1:G100")" )

"Nick Hodge" wrote in message
...
James

In the code below the column selected is E. The 2nd column in the
range D1:G100

Sub SelectColumnInRange()
Dim rng As Range
Set rng = Range("D1:G100")
rng.Columns(2).Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"James Cornthwaite" wrote in message
...
If I have a range object passed to a function, eg, rng, does anybody
know how to just select column A from this larger range, rng

Is it some sort of union i have to do.

Many thanks
james