Inserting a row after every seven lines

  • Thread starter Thread starter Charles Shahar
  • Start date Start date
C

Charles Shahar

Hello:
I have an Excel problem I would really appreciate help with. I have a very
long mailing list for a research project. The list contains rows of names &
addresses. I need to divide the list into blocks or parcels of seven names
(lines) each. How do I get Excel to insert a row after every seventh line?
If I tried to do this manually it could take hours! Thanks a bunch for your
help...

-Charles
 
Try running a macro like:

Sub InsertRow()
Dim iLastRow As Integer
Dim i As Integer

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow + 1 To 1 Step -7
With Cells(i, "A")
.EntireRow.Insert
End With
Next i

Application.ScreenUpdating = True

End Sub

--
Press ALT+F11, go to Insert > Module, paste in the code
above, close VBE, and run the macro from XL under Tools.

HTH
Jason
Atlanta, GA
 
Fantastic Jason!! Thanks a bunch!!


Jason Morin said:
Try running a macro like:

Sub InsertRow()
Dim iLastRow As Integer
Dim i As Integer

Application.ScreenUpdating = False

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow + 1 To 1 Step -7
With Cells(i, "A")
.EntireRow.Insert
End With
Next i

Application.ScreenUpdating = True

End Sub

--
Press ALT+F11, go to Insert > Module, paste in the code
above, close VBE, and run the macro from XL under Tools.

HTH
Jason
Atlanta, GA
Click to expand...
 
Grüezi Charles

You are in the German-NG; you know that?

Charles Shahar schrieb am 04.06.2004
I have an Excel problem I would really appreciate help with. I have a very
long mailing list for a research project. The list contains rows of names &
addresses. I need to divide the list into blocks or parcels of seven names
(lines) each. How do I get Excel to insert a row after every seventh line?
If I tried to do this manually it could take hours! Thanks a bunch for your
help...
Click to expand...

Manually it takes about 1 minute when you do it like this:

In an temporary column right beside your datas write in the first cell '1'
and in the one right below '2', select both and doubleklick the
autofill-handle (little black square in the bottom right corner of the
selection).

Now move to the end of your temporary colunm, write right below the last
numer '7' and below that '14', select both of this new cells, 'grab' the
autofill-handle with the left mousebutton and move downwards until the
shown number is higher than the amount of your datas.

Now move back to the top, select *one* cell in your temporary column and
click the 'A-Z'-button.

Your empty rows are added now - delete the temporary column.

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
 
Back
Top