Insert multiple rows with data

[sudipto3003]

Dear Friends,

In col "A" I have some expenses heads, I want to insert a series of numbers (which are fixed) at col "B" after each expense head. Copy paste is not the easiest way for me because the number of expense heads are huge. I have also attach herewith a sample file where in first sheet I have mentioned the data table and in sheet"Required" I have mentioned how I need this. thanks in advance.

 

[Khalid NGO]

Hi Sudipta,

I think you need continuous serial numbers 1 to 11 (fixed), if so, check this in C2 of Required sheet:

=1001+MOD(ROW(A1)-1,11)

copy down.

Regards,

 

[sudipto3003]

Dear Khalid,

thanks for your quick reply, yes the number of serial numbers are fixed say there are 240 numbers but they are not in ascending or descending orders. the numbers are variable. May be I am not cleared to you sorry. again I have attached another sample file here. I want to put those 240 numbers each after the text. any formula that will create 239 blank rows each after text and will copy those 240 numbers at the very next column.

 

[Khalid NGO]

Can you use a helper column?

 

[sudipto3003]

sure dear Khalid.

 

[Khalid NGO]

See the attached.

Column H is helper of your serial nos.

Regards,

 

[sudipto3003]

Dear Khalid,

Thank you for your reply, but how should I insert number of 239 blank rows each after the text. In the original sheet all the texts are one after another. I have to insert 239 rows each after the text and then to put your formula at the next column.

 

[Khalid NGO]

Dear Khalid,

Thank you for your reply, but how should I insert number of 239 blank rows each after the text. In the original sheet all the texts are one after another. I have to insert 239 rows each after the text and then to put your formula at the next column.

Hi Sudipta,
Sorry for the delay, I am not 100% sure about your new requirement.
You will need VBA for inserting blank rows, but I guess you can use the following (hard-coded) formula in B2:

=IF(1+MOD(ROW(A1)-1,5^10)=1,"Preference Share Capital", IF(1+MOD(ROW(A1)-1,5^10)=241,"Equity Share Capital", IF(1+MOD(ROW(A1)-1,5^10)=481,"Capital Rseserve on Amalgamation","")))

Copy down.

[cell will not be real blank]

Regards,

 

[sudipto3003]

Dear Khalid,

No need to say sorry for delay, thanks again for given your valuable time for me but sorry to say this not clear my problem. I am waiting if some one can help me from the group.

 

[Nebu]

Hi:

Find the code

Sub test()
Application.ScreenUpdating = False
Dim arr()
i& = Me.Cells(Rows.Count, "B").End(xlUp).Row
arr = Array(1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011)
For r& = i To 3 Step -1
    Rows(r).Resize(10).Insert
Next
i& = Me.Cells(Rows.Count, "B").End(xlUp).Row
For j& = 2 To i Step 11
    Me.Range("C" & j & ":C" & j + 10) = WorksheetFunction.Transpose(arr)
Next
Application.ScreenUpdating = True
End Sub

Click on the run button to get the results.

Thanks

 

[sudipto3003]

hi Nebu,

Thank you, its working smoothly and fulfil my requirement. thanks a lot.