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

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

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

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

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

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