does anybody know how to select the entire first column of a defined range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

should read rng.columns(1).select sorry
but still doesnt work

would rng(xldown, 1) select column 1 with rows 1 to the last one containing
xl data work???

thanks
james

"James Cornthwaite" wrote in message
...
does anybody know how to select the entire first column of a defined range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

Hi James, I'm selecting the first column of a range called "Test" in the code
below:

Sub SelFirstCol()
'Select first column of defined range
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

Um, one other thing, you'll need to select the sheet the desired range
resides in before you attempt to select the column.

Sub SelFirstCol()
'Select first column of defined range
Sheets("BlahBlah").select
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

ah right


I have udf (as below) which has a range passed to it from a sheet

findnewnominal(rng as Range) as Integer
dim cell as range


rng.columns(1).select

For Each cell in selection
blah
blah

next cell

end function

but the above doesn't work
Am i correct in thinking that word selection is equal to column 1 of range
(i'm new to VBA, used to JAVA more therefore would expect to see selection =
rng.columns(1).select). And what you say about selecting the sheet
containing the range first, is this really neccessary when passing the
reference of a range to a function.

Can u see any other reason why my code above doesnt work.

Many Thanks
James

"Paul Mathews" wrote in message
...
Um, one other thing, you'll need to select the sheet the desired range
resides in before you attempt to select the column.

Sub SelFirstCol()
'Select first column of defined range
Sheets("BlahBlah").select
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined
range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

UDF's called from formulas in worksheet cells can't select ranges.

why not just use:

for each cell in rng.columns(1)



James Cornthwaite wrote:

ah right

I have udf (as below) which has a range passed to it from a sheet

findnewnominal(rng as Range) as Integer
dim cell as range

rng.columns(1).select

For Each cell in selection
blah
blah

next cell

end function

but the above doesn't work
Am i correct in thinking that word selection is equal to column 1 of range
(i'm new to VBA, used to JAVA more therefore would expect to see selection =
rng.columns(1).select). And what you say about selecting the sheet
containing the range first, is this really neccessary when passing the
reference of a range to a function.

Can u see any other reason why my code above doesnt work.

Many Thanks
James

"Paul Mathews" wrote in message
...
Um, one other thing, you'll need to select the sheet the desired range
resides in before you attempt to select the column.

Sub SelFirstCol()
'Select first column of defined range
Sheets("BlahBlah").select
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined
range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James




--

Dave Peterson

Hi again James,

Yep, range selection is a bit goofy in Excel when the ranges occupy
different sheets . Notwithstanding this, perhaps you don't need to do any
range/cell selection at all but rather just retrieve values from the column
in question. What you might want to try (and this gets around the sticky
cell/sheets selection stuff) is something along the lines of the following:

findnewnominal(rng as Range) as Integer
dim i as long

For i = 1 to rng.rows.count
if rng.cells(i,1).value = blah blah then
do blah blah blah
endif

end function


"James Cornthwaite" wrote:

ah right


I have udf (as below) which has a range passed to it from a sheet

findnewnominal(rng as Range) as Integer
dim cell as range


rng.columns(1).select

For Each cell in selection
blah
blah

next cell

end function

but the above doesn't work
Am i correct in thinking that word selection is equal to column 1 of range
(i'm new to VBA, used to JAVA more therefore would expect to see selection =
rng.columns(1).select). And what you say about selecting the sheet
containing the range first, is this really neccessary when passing the
reference of a range to a function.

Can u see any other reason why my code above doesnt work.

Many Thanks
James

"Paul Mathews" wrote in message
...
Um, one other thing, you'll need to select the sheet the desired range
resides in before you attempt to select the column.

Sub SelFirstCol()
'Select first column of defined range
Sheets("BlahBlah").select
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined
range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

thats great cheers


"Paul Mathews" wrote in message
...
Hi again James,

Yep, range selection is a bit goofy in Excel when the ranges occupy
different sheets . Notwithstanding this, perhaps you don't need to do any
range/cell selection at all but rather just retrieve values from the
column
in question. What you might want to try (and this gets around the sticky
cell/sheets selection stuff) is something along the lines of the
following:

findnewnominal(rng as Range) as Integer
dim i as long

For i = 1 to rng.rows.count
if rng.cells(i,1).value = blah blah then
do blah blah blah
endif

end function


"James Cornthwaite" wrote:

ah right


I have udf (as below) which has a range passed to it from a sheet

findnewnominal(rng as Range) as Integer
dim cell as range


rng.columns(1).select

For Each cell in selection
blah
blah

next cell

end function

but the above doesn't work
Am i correct in thinking that word selection is equal to column 1 of
range
(i'm new to VBA, used to JAVA more therefore would expect to see
selection =
rng.columns(1).select). And what you say about selecting the sheet
containing the range first, is this really neccessary when passing the
reference of a range to a function.

Can u see any other reason why my code above doesnt work.

Many Thanks
James

"Paul Mathews" wrote in message
...
Um, one other thing, you'll need to select the sheet the desired range
resides in before you attempt to select the column.

Sub SelFirstCol()
'Select first column of defined range
Sheets("BlahBlah").select
Range("Test").Columns(1).Select
End Sub


"James Cornthwaite" wrote:

does anybody know how to select the entire first column of a defined
range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

"James Cornthwaite" wrote in message
...
does anybody know how to select the entire first column of a defined range
object.

rng.columns(2).select doesnt seemt to work

Thanks
James

Your code selects only the cells in the second column of rng - i.e. only the
intersection of rng with the entire column. If you want to select the entire
column, of which part is the second column of cells in rng, you need to use:

rng.Columns(2).EntireColumn.Select