Adding rows within a list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nihad - 22 Feb 2006 16:38 GMT

I have a long list with no spaces, all in one column. I am trying to
add 7 blank lines in between each row. What is the easiest way to
achieve this?

Thanks in advance

Signature

nihad

Reply to this Message

BadgerMK - 22 Feb 2006 16:59 GMT

Hi

Try the following (I named my list Test)

Dim r As Object
Dim intCount As Integer
intCount = 1

For Each r In Range("Test")
If r.Value = "" Then

Else
r.Offset(1, 0).Select
intCount = 1
Do Until intCount > 7
Selection.EntireRow.Insert
intCount = intCount + 1
Loop
End If

Next

Reply to this Message

Ron de Bruin - 22 Feb 2006 17:05 GMT

Hi nihad

Try this macro

Sub test()
Application.ScreenUpdating = False
Dim R As Long
Dim rng As Range
Set rng = ActiveSheet.UsedRange
For R = rng.Rows.Count To 1 Step -1
rng.Rows(R + 1).Resize(7).EntireRow.insert
Next R
Application.ScreenUpdating = True
End Sub

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> I have a long list with no spaces, all in one column. I am trying to
> add 7 blank lines in between each row. What is the easiest way to
> achieve this?
>
> Thanks in advance

Reply to this Message

Ardus Petus - 22 Feb 2006 17:13 GMT

Sub SpaceSeven()
Dim irow As Long
For irow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
Rows(irow).Resize(7).Insert shift:=xlDown
Next irow
End Sub

HTH

--
AP

> I have a long list with no spaces, all in one column. I am trying to
> add 7 blank lines in between each row. What is the easiest way to
> achieve this?
>
> Thanks in advance

Reply to this Message