Inserting rows in between the data

[FREEAATMA]

Hi Guys,

I'm struggling big time with a small issue in excel. Pls help...

I'm having a data in two col. The issue here is I need to insert a row in between in such a way that the value in each cell is replicated vertically in the next cell. This is required 11 times for each record in the data.
E.g. if I have values 1,2,3,4... in cells A1, A2, A3, A4... I need a formula which can insert 11 rows in between A1 and A2 and then copies the value in cell A1 I.e. "1" in subsequent cells 11 times till A11. This is repeated till the last cell in the data.

 

[p45cal]

Assuming columns A and B, two alternative macros:
1. Inserts entire rows across the sheet:

Sub blah()
LastRow = Cells(Rows.Count, 1).End(xlUp)
For rw = LastRow + 1 To 2 Step -1
  Rows(rw).Resize(10).Insert
  Cells(rw - 1, 1).Copy Cells(rw, 1).Resize(10)
Next rw
End Sub

2. Inserts only into columns A and B:

Sub blah2()
LastRow = Cells(Rows.Count, 1).End(xlUp)
For rw = LastRow + 1 To 2 Step -1
  Cells(rw, 1).Resize(10, 2).Insert Shift:=xlDown
  Cells(rw - 1, 1).Copy Cells(rw, 1).Resize(10)
Next rw
End Sub