Automatic Insert

[kbyouth]

I am trying to write a macro that inserts a row after each row of data. I have over 16000 rows of data that I need to import to Quickbooks, but I need a blank row after each transaction on the spreadsheet. I also need to insert some text on the row that is being inserted. But the main problem is get the macro to do insertions after each row on the entire range of data.

Please assist

[Nimrod]

This should insert a blank row after each row

Sub InsertSpace()
    bottom = Cells(65536, 1).End(xlUp).Row
        For rw = 1 To (bottom * 2) Step 2
            Range(rw & ":" & rw).Insert Shift:=xlDown
        Next
End Sub

[dimrod]

With 16000 rows, I'm willing to bet that doing it manually will be a lot quicker than Nimrod's macro :-

- In a blank column, fill with sequential numbers from your first data row to the last data row.

- copy these numbered cells and paste immediately below.

- Sort all of your data by the numbered column

- Delete the numbered column

[Nimrod]

Neat solution dimrod... though my solution wasn't that sloooow... I just ran it with 16000 lines of data in 10.26 seconds.

By the way did you time your solution ???

Sub InsertSpace()
time1 = (Format(Time, "mm.sss"))
    bottom = Cells(65536, 1).End(xlUp).Row
        For rw = 1 To (bottom * 2) Step 2
            Range(rw & ":" & rw).Insert Shift:=xlDown
        Next
time2 = (Format(Time, "mm.sss"))

MsgBox (time1 & "     " & time2)
End Sub

[ This Message was edited by: Nimrod on 2002-05-23 19:01 ]

[Colo]

Hi All,

In case only value property is needed, using array makes it fast.
Here is a sample I made.

With 16000 Rows * 10 Columns 1.28sec Win2K XL97 256MB Celeron600

Sub InsertSpaceSmple()

    Dim s As Single, tmp, arr(), i As Long, j As Integer

    s = Timer

    With ActiveSheet.UsedRange

        tmp = .Value

        ReDim arr(UBound(tmp, 1) * 2, UBound(tmp, 2))

        For i = LBound(tmp, 1) To UBound(tmp, 1)

            For j = LBound(tmp, 2) To UBound(tmp, 2)

                arr(i * 2 - 1, j) = tmp(i, j)

            Next

        Next

        .Resize(.Rows.Count * 2).Value = arr

    End With

    MsgBox Timer - s

End Sub

[Nimrod]

Veerrrry Cooool , Colo :
If you don't mind I'm going to disect and keep a copy of the code.

[Colo]

My Pleasure

[dimrod]

On 2002-05-23 18:59, Nimrod wrote:
Neat solution dimrod... though my solution wasn't that sloooow... I just ran it with 16000 lines of data in 10.26 seconds.

By the way did you time your solution ???

Sub InsertSpace()
time1 = (Format(Time, "mm.sss"))
    bottom = Cells(65536, 1).End(xlUp).Row
        For rw = 1 To (bottom * 2) Step 2
            Range(rw & ":" & rw).Insert Shift:=xlDown
        Next
time2 = (Format(Time, "mm.sss"))

MsgBox (time1 & "     " & time2)
End Sub

[ This Message was edited by: Nimrod on 2002-05-23 19:01 ]

I don't understand what sort of time is returned by your code :-
Format(Time, "mm.sss"))

I think this code is producing an incorrect run time of 10.26 seconds.

With data in one column only (A1:A16000),
I have now timed your macro and it took 2 minutes and 59.06 seconds.

With screen updating turned off, it took 2 minutes 5.45 seconds.

I have not timed how long it would take to do manually, but I'm sure it's less than that.

Here's a macro based on my suggested manual method.
With the same amount of data (in A1:A16000 only), the run time is only 0.33 seconds :-

Sub InsertRows()
Dim t!, rws&
t = Timer
rws = [A65536].End(xlUp).Row
Application.ScreenUpdating = False
Columns(1).Insert
With [A1]
.Value = 1
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=rws
End With
Range([A1], [A1].End(xlDown)).Copy Cells(rws + 1, 1)
Range([A1], [A1].End(xlDown)).EntireRow.Sort Key1:=Range("A1"), Header:=xlNo
Columns(1).Delete
Application.ScreenUpdating = True
MsgBox Timer - t
End Sub


I can't get Colo's macro to do what the original poster requested - so I can't really comment on that.
However, with data in 16000 rows and in 10 columns, my macro took 0.82 seconds. Colo mentioned that his took 1.28 seconds.

[Ivan F Moala]

Nice solution Dimrod aka ??

[dimrod]

On 2002-05-24 02:47, Ivan F Moala wrote:
Nice solution Dimrod aka ??

AKA ?

I'll let you work it out.

Clue : Try to think of synonyms for "dim" and "rod".