Insert a Range

[Steve]

Hello. I am trying to write code that will do an xlendup on a
worksheet to find the last row, and then offset 9 rows up. After I
have the range, copy the range and insert on the next available line.
I tried some code (below) but couldnt get it to work. Any ideas?
Thanks!

lastrow = ActiveSheet.Cells(Cells.Rows.Count, "D").End(xlUp).Row
lastrange = ActiveSheet.Rows(lastrow.Resize(9, 0))

Rows(lastrange).Copy.
Rows(lastrange).Insert Shift:=xlDown

 

[Per Jessen]

Hi Steve

Try this, (I assume you want to copy entire row)

Sub test()
Dim LastRange As Range
LastRow = ActiveSheet.Range("D65536").End(xlUp).Row
Set LastRange = ActiveSheet.Rows(LastRow - 8).Resize(9, 1)

LastRange.EntireRow.Copy
LastRange.Insert Shift:=xlDown
End Sub

Regards,

Per

 

[Per Jessen]

Hi

Just a little thing...

If you only want to copy column D use this line:

Set LastRange = ActiveSheet.Columns(4).Rows(LastRow - 8).Resize(9, 1)

Regards,

Per

 

[Ivyleaf]

Hi,

Just another option:

Sub RepCell()
Dim TempRng As Range

Set TempRng = ActiveSheet.Range("D65536").End(xlUp)
TempRng.Offset(1, 0) = TempRng.Offset(-9, 0)
End Sub

Sub RepRow()
Dim TempRng As Range

Set TempRng = ActiveSheet.Range("D65536").End(xlUp).EntireRow
TempRng.Offset(1, 0) = TempRng.Offset(-9, 0).Value
End Sub

The first will just do one cell, the second does the entire row. This
code doesn't actually insert a row, it just copies the values down.
I'm not saying one is better than the other, it just depends what you
want to achieve. Inserting a row will move anything below in other
columns down, while the method in these two won't.

Cheers,
Ivan.