Excel 2010 Insert Rows and Move Data

I have an Excel workbook of approximately 1500 rows with data in columns A and B.  I need to insert a row and move the data in column B to the row that was recently inserted. Next I need to insert another row so that there is a blank row between two rows with data.

Is there a VBA procedure a macro that could do this for me?

Thanks for your help,

Jeremy


Excel 2010 Insert Rows and Move Data

I have an Excel workbook of approximately 1500 rows with data in columns A and B.  I need to insert a row and move the data in column B to the road that was recently inserted. Next I need to insert another row so that there is a blank row between two rows with data.

Is there a VBA procedure a macro that could do this for me?

Thanks for your help,

Jeremy
Jeremy-MAsked:
Who is Participating?
 
FlysterCommented:
Try this macro. I believe it will give you the results you're looking for. As always, with trying any macro the first time, be sure to have a backup of your data available :)

Flyster
Sub AddRows()
Dim LstRow As Integer
Dim Ctr As Integer


LstRow = Range("A65536").End(xlUp).Row * 3

  For Ctr = 2 To LstRow Step 3
     Rows(Ctr & ":" & Ctr).Select
     Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
     Range("B" & Ctr - 1).Select
     Selection.Cut
     Range("B" & Ctr).Select
     ActiveSheet.Paste
     Rows(Ctr + 1 & ":" & Ctr + 1).Select
     Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  Next Ctr

End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.