range offset

[Douvid]

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 Molloy]

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

 

[kiat]

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

Is that what you want?

 

[keepitcool]

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]

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 ?

 

[Tom Ogilvy]

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 B25), but
the A2:A5 is in the new column, the B2:C5 is part of the original selection
and current selection and D25 is not selected.

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

selection.Resize(,1).Select