Using MS Access VBA to insert a row into an Excel worksheet, between existing rows without replacing data.

Question

I need help with some simple syntax. Most of what i find is more complex than i require and i cannot seem to be able to figure out how, within my VBA procedure to be able to insert a new blank row directly after the existing 2nd row, but without replacing it. No matter what i try, it successfully inserts the row, but it replaces the exiting data!  My most recent attempt:

 xlWSh.Range("2:2").Select

ActiveCell.Offset(1).EntireRow.Insert

have also tried simply:

 xlWSh.Range("2:2").Select

ActiveCell.EntireRow.Insert

Thank you for your help!

Answer 1

You probably need to include a shift of some sort with your insert so existing data gets moved.

Something like:

ActiveCell.EntireRow.Insert shift:=xlShiftDown

Answer 2

Thank you for your response. I tried that and received the message citing Object variable or with block variable not set. I tried to use it within the with block that i show below but it wasn't happy:)

I did some more hunting and modified an example i found which works fine inserting a row at the the top of the worksheet, but i need to insert it directly after the first row (and before the 2nd row) -  always at this location. Here is the simple code that i inserted however i can't get it to insert as a new 2nd row:

Dim lngXtra As Integer

lngXtra = 1

With xlWSh

    Call .Range("A1:A" & lngXtra).EntireRow.Insert

End With

Is there a simple modification to the above that you could recommend?

Answer 3

Dim lngXtra As Integer lngXtra = 2

With xlWSh

Call .Range("A2:A" & lngXtra).EntireRow.Insert

End With

Answer 4

This one line seemed to work for me:

Range("A2").EntireRow.Insert xlDown

Even without the xlDown worked for me as well:

Range("A2").EntireRow.Insert

Answer 5

peiyezhu, Your suggestion solved my issue.  Before your response I entered one line of code (without any "WITH" statements): "xlWSh.Rows(2).EntireRow.Insert"

So before I wrap this up, I wondered if you or Butch would recommend using one method (the above line) over the other (the little "WITH" group).

Thank you again!!