Hi,
I have a range, I'm adding a column before the first one
of the range then I need to select a specific range in
that new column according to the first selected range.
Lets say my original range was A2:B5, now the new column
is becoming A and my original range (B2:C5), so how do I
select in that column the range of rows corresponding to
the original range (A2:A5)??
Using the range.offset synthax is selecting a new range of
the size of the original one but I need less than that.
Can someone light my way for that one ???
TKS

Make sure that the original range is range named. Then
when you insert a column, the named range will shift too.

to access the first column, just point to it

for example

dim cell as range

for each cell in Range("MyRange").Columns(1).Cells
'''
Next

or
Dim rCol1 as Range
SET rCol1 = Range("MyRange").Columns(1)


Patrick Molloy
Microsoft Excel MVP












-----Original Message-----
Hi,
I have a range, I'm adding a column before the first one
of the range then I need to select a specific range in
that new column according to the first selected range.
Lets say my original range was A2:B5, now the new column
is becoming A and my original range (B2:C5), so how do I
select in that column the range of rows corresponding to
the original range (A2:A5)??
Using the range.offset synthax is selecting a new range
of
the size of the original one but I need less than that.
Can someone light my way for that one ???
TKS
.

Columns(1).Insert
Range(Cells(Selection.Row, 1), Cells(Selection.Rows.Count + Selection.Row -
1, 1)).Select

Is that what you want?

"Douvid" wrote in message
...
Hi,
I have a range, I'm adding a column before the first one
of the range then I need to select a specific range in
that new column according to the first selected range.
Lets say my original range was A2:B5, now the new column
is becoming A and my original range (B2:C5), so how do I
select in that column the range of rows corresponding to
the original range (A2:A5)??
Using the range.offset synthax is selecting a new range of
the size of the original one but I need less than that.
Can someone light my way for that one ???
TKS

in addition to Patrick..

have a look at .Resize
it's often used together with .Offset

set r = Range("B1:C10")
set r = r.Offset(0,-1).Resize(,r.Columns.Count + 1)


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Douvid" wrote:

Hi,
I have a range, I'm adding a column before the first one
of the range then I need to select a specific range in
that new column according to the first selected range.
Lets say my original range was A2:B5, now the new column
is becoming A and my original range (B2:C5), so how do I
select in that column the range of rows corresponding to
the original range (A2:A5)??
Using the range.offset synthax is selecting a new range of
the size of the original one but I need less than that.
Can someone light my way for that one ???
TKS

Patrick,
The thing is that I need to autofill the added column.So I
need to provide a destination range which has to be in the
new column starting at the row the named range start
untill it ends. But the range is always different. Am I
understandable ?

-----Original Message-----
Make sure that the original range is range named. Then
when you insert a column, the named range will shift too.

to access the first column, just point to it

for example

dim cell as range

for each cell in Range("MyRange").Columns(1).Cells
'''
Next

or
Dim rCol1 as Range
SET rCol1 = Range("MyRange").Columns(1)


Patrick Molloy
Microsoft Excel MVP












-----Original Message-----
Hi,
I have a range, I'm adding a column before the first one
of the range then I need to select a specific range in
that new column according to the first selected range.
Lets say my original range was A2:B5, now the new column
is becoming A and my original range (B2:C5), so how do I
select in that column the range of rows corresponding to
the original range (A2:A5)??
Using the range.offset synthax is selecting a new range
of
the size of the original one but I need less than that.
Can someone light my way for that one ???
TKS
.

.

Selection.columns(1).EntireColumn.Insert
selection.Resize(,1).Select

When you insert a column to the left of the selection, the selection shifts
to start in the new column

for your example, if I have A2:C5 selected, and I do

Selection.columns(1).EntireColumn.Insert

then I still have A2:C5 selected (my original selection is now B2:D5), but
the A2:A5 is in the new column, the B2:C5 is part of the original selection
and current selection and D2:D5 is not selected.

So to only select the same rows in the new column A i then do

selection.Resize(,1).Select

--
Regards,
Tom Ogilvy


"Douvid" wrote in message
...
Patrick,
The thing is that I need to autofill the added column.So I
need to provide a destination range which has to be in the
new column starting at the row the named range start
untill it ends. But the range is always different. Am I
understandable ?

-----Original Message-----
Make sure that the original range is range named. Then
when you insert a column, the named range will shift too.

to access the first column, just point to it

for example

dim cell as range

for each cell in Range("MyRange").Columns(1).Cells
'''
Next

or
Dim rCol1 as Range
SET rCol1 = Range("MyRange").Columns(1)


Patrick Molloy
Microsoft Excel MVP












-----Original Message-----
Hi,
I have a range, I'm adding a column before the first one
of the range then I need to select a specific range in
that new column according to the first selected range.
Lets say my original range was A2:B5, now the new column
is becoming A and my original range (B2:C5), so how do I
select in that column the range of rows corresponding to
the original range (A2:A5)??
Using the range.offset synthax is selecting a new range
of
the size of the original one but I need less than that.
Can someone light my way for that one ???
TKS
.

.