Insert multiple empty rows

Ladies and Gentlemen,

Every week i get a very big excel file, with about 2000 rows.
I need to insert 4 empty rows between all different values in Column A.
I have a macro wich works, but it inserts only one empty row, and
I cant find a way to insert 4 rows. Maybe somenone can explain where
in der formula i need to write in to insert 4 empty rows, instead 1?

My macro code for this is:

Option Explicit

Sub insert()
Dim lngI As Long
For lngI = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(lngI, 1) <> Cells(lngI - 1, 1) Then Rows(lngI).Insert Shift:=xlDown
Next
End Sub



INSERT EMPTY ROW.jpg

Afterwards i need to paste some datas and formulas in these empty rows.
Is it possible to do this with VBA, too?

I hope that i could explain my concern understandeble, my english is not the best.

cheers

mandura

Please use code tags when posting. Maybe:

Please Login or Register  to view this content.

This should do the first bit !!

Please Login or Register  to view this content.

Regards Mick

Thank you very, very much!!

it works perfectly, after a little change.

in the first row of your formula you forgot to write a "r" down
after this little repair it still wasnt working.

after deleting "Rows" from "Sub InsetRows()" it finaly worked.

the vba code that i need is finaly:

Sub Insert()
Dim LR As Integer, Counter As Integer
LR = Cells(Rows.Count, 1).End(xlUp).Row
For Counter = LR To 2 Step -1
If Cells(Counter, 1) <> Cells(Counter - 1, 1) Then
Rows(Counter & ":" & Counter + 3).Insert Shift:=xlDown
End If
Next Counter
End Sub


thank you very much again mate!!!

Originally Posted by MickG

This should do the first bit !!

Please Login or Register  to view this content.

Regards Mick

Thanks for your reply, but unfortunately this is not the solution for my
request. this code is inserting for empty rows after between every single row.
i need 4 empty rows between rows were the data or value is different.

Unfortunately I misread your requirements, but thank you for your courteous reply.

Originally Posted by MickG

Unfortunately I misread your requirements, but thank you for your courteous reply.

misreading can happen, but thank you for your effort anyway!

Originally Posted by NeedForExcel

@mandura - Glad it worked.. Sorry for the trouble!

But the 2 issues you pointed out, not very sure about them.. :O

a little bit trouble, but a very big benefit for me!!

thanks again!

Originally Posted by JOHN H. DAVIS

Please use code tags when posting. Maybe:

Please Login or Register  to view this content.

i am sorry, but i dont understand? what are code tags?

Originally Posted by mandura

i am sorry, but i dont understand? what are code tags?

Please review the Forum Rules. Refer too Rule No. 3.

http://www.excelforum.com/forum-rule...rum-rules.html

my second need is to insert data and 2 formulas in these 4 empty rows.
it would be great if anyone could help me.

i need the auto sum i column "P", and in column "O" i want wo copy the
number of the bill.

example.jpg

i hope this is possible with a macro code?

i tried to insert a excel file, but i could not find a way to do it.