Range.Insert query

[erazmus]

Hi Guys,

I was trying to understand how to use the Range.Resize property but
after much googling and reading of help files gave up on that and tried
another tack.

That was to create a completely new range by using Range( cells( row,
column), cells( row, column)).

The following sub works as I would expect it to, but one thing I don't
understand, which highlights my ignorance of what I am actually doing,
is this. In the following code there are two commented MsgBoxes, the
first MsgBox produces an expected address, the second MsgBox produces a
different result I would assume because of the shift of cells.

Why does the shift of cells cause the address of my variable to change
though?

Thanks in advance,
Deon.

Sub testresize()

Dim testRange As Range
Dim newRange As Range
Dim addRows As Integer
Dim rngStartColumn As Integer
Dim rngStartRow As Integer
Dim rngEndColumn As Integer
Dim rngEndRow As Integer

addRows = 6
rngStartColumn = 0
rngStartRow = 0
rngEndColumn = 0
rngEndRow = 0

Set testRange = Range("B66")
Set newRange = Range("B66")

rngStartColumn = testRange.Column
rngEndColumn = rngStartColumn + testRange.Columns.Count - 1
rngStartRow = testRange.Row
rngEndRow = rngStartRow + testRange.Rows.Count - 1

With ActiveSheet
Set newRange = .Range(.Cells(rngStartRow, rngStartColumn),
..Cells(rngEndRow + addRows, rngEndColumn))

'' This MsgBox produces the expected result
MsgBox "Address before shift " & newRange.Address
newRange.Name = "xRange"
.Range("xRange").Insert Shift:=xlShiftDown

'' The MsgBox doesn't produce an expected result?
MsgBox "Address after shift " & newRange.Address
End With


End Sub

 

[swiss-jimbo]

Hi

When you use the constant xlShiftDown you are changing the row property
of the object newRange from 6 to 13.

newRange.Name = "xRange"
.Range("xRange").Insert Shift:=xlShiftDown

If you are trying to write a macro that shifts data around and then
produces a report I would first use the xlShiftDown and then reference
the desired range with a new variable.

Hope this helps

/ James