Excel 2010 vba: Why does this offset operation fail?

Question

CellRng.Offset(1, 0).EntireRow.Insert Shift:=xlDown

CellRng references the first cell on a row. I want to insert a row below.

This is part of a work-around to the problem where a copy and Insert copied cells fails. I wont work manually and in VBA it throws the runtime error 1004. I have a longer bit of code using a couple of ranges to complete the insert followed by the copy, but the above would be more elegant if it would work...

Answer 1

In a test workbook (Excel 2010 x32 on Win7 x64) , this works for me to insert a row below the target cell:

Sub test()

   Dim CellRng As Range

   Set CellRng = ActiveSheet.Range("A4")

   CellRng.Offset(1, 0).EntireRow.Insert Shift:=xlDown

End Sub

So, the first thing to check is your assignment of CellRng to make sure it is valid.

Answer 2

It helps if the worksheet is not protected! Error 1004 is so unhelpful... Maybe I will remember this one now!

Thank you for confirming the code and for the prompt response :)