VBA insert entire Rows in large numbers

[Bearlord]

Hello

I'm once again troubling this forum's community with a puzzle that has been driving me crazy for the past 2 days.

I wrote a code that has to insert rows when certain conditions are met. The code finds the conditions, reads a number (X) in a row that meets those conditions and inserts X amount of rows. These has to go on for the entire sheet untill all numbers have been found and all the rows inserted.

My problem is that the code takes a reaaally long time to execute. The first time I tried it out in a real case scenario ( I had tested it in smaller samples of data) it ran for nearly half an hour before I thought it was stuck... so using the glamorous "esc" method I ended it. Using the debug option I found out, to my surprise, that the code wasn't stuck! It was working... really slowly but it was working. All variables had valid values and were increasing accordingly to the loop they were in. It was just taking a really long while. I ran it a few more times in smaller versions and came to the conclusion that the code does work, but it is slow.

I made several tests to see what part of the code was being the slow one, so by placing breakpoints and debug prints I found out that the insert function itself was the one that was being slow. I believe this is somehow related to the fact that whenever I insert a row, it has to displace ALL the rows below it to a different row.

I was wondering if it was possible to insert a range of rows at once, because in some cases my code has to insert over a thousand rows consecutively and it does it one by one. I think that if I can insert the 1k rows at once it would really improve my code's speed. Something like:

Insert.Range(X_amount_of_rows).EntireRow

Or somewhere among those lines... instead of inserting row by row untill the 1k rows are inserted and then jump to the next row that meets the condition. One thing that might be important to mention is that the files I am working with are really large. I have to process over 40k rows in each file and I estimate that the code would have to insert at least another 40k rows in each file... hence my need to "optimize" the code.

Here is a trimmed version of my code:

Sub NewRow()

Application.ScreenUpdating = False

    Dim X As Integer
    Dim hCell As Range
    Dim Y As Integer
    Dim Already As Boolean
    Dim aCounter As Integer
    
    aCounter = 0
    Dim LastRow5 As Integer
    
    LastRow5 = Range("D4224:D45000").End(xlDown).Row
    Debug.Print LastRow5
    Already = False
    For Each hCell In Range(Cells(4224, "DE"), Cells(LastRow5 + 40000, "DE"))
        'hCell.Select
        If Already = False Then
            X = hCell.Value
            X = X - 1
            hCell.Select
            If hCell.Value <> "" Then
                For Y = 1 To X
                    
                    hCell.Offset(1, 0).EntireRow.Insert
                    'LastRow5 = LastRow5 + 1
                    aCounter = 0
                    
                Next Y
                If hCell.Offset(0, 1) <> "" Then
                    Already = True
                End If
                
            End If
        ElseIf hCell.Value <> "" Then
            Already = False
        ElseIf hCell.Value = "" Then
            aCounter = aCounter + 1
        End If
        If aCounter > 25 Then
            Exit For
        End If
    Next hCell
   
    Application.ScreenUpdating = True

End Sub

Any ideas?

Thanks a LOT for any help =D I am new to excel vba and I know my methods are really slow... so any help will be really appreciated!

Bear

 

[patel45]

you can insert many rows with
range("A50:A100").EntireRow.Insert

 

[Bearlord]

you can insert many rows with
range("A50:A100").EntireRow.Insert

Hey patel45! Thanks a lot for the quick response. I tried doing that and it does work, but it still takes a long while. any ideas on what is causing the low speed? Is there any other way I could optimize/optimise the code?

 

[mirabeau]

Hey patel45! Thanks a lot for the quick response. I tried doing that and it does work, but it still takes a long while. any ideas on what is causing the low speed? Is there any other way I could optimize/optimise the code?

Hi Bear,

Just to get a handle on what you're trying to do, could you try the following experiment.

In a blank worksheet, fill the first 10 cells in column A with some values (anything recognizable). Then in column B put in a few numbers, say B3=4 and B7 =2, which are the number of rows you want to insert at that point.

Run the following macro. Does it give the general sort of result that you want? If so it's easily extended to 50k or more rows with lots of insertions and to different ranges etc., and shouldn't take very long at all to execute.

Sub insertrows()
Const m& = 3 * 10 ^ 5
Dim b(m) As Boolean
Dim u&(1 To m, 1 To 1)
Dim n&, j&, q&, c&

n = Cells(Rows.Count, 1).End(3).Row
For j = 1 To n
    q = q + Cells(j, "b") + 1
    b(q) = True
    u(j, 1) = q
Next j

For j = 1 To q
    If Not b(j) Then c = c + 1: u(n + c, 1) = j
Next j

Cells(1, 3).Resize(q) = u
Cells(1, 1).Resize(q, 3).Sort Cells(1, 3), Header:=xlNo
Range("c:c").ClearContents
End Sub

 

[Bearlord]

Hello mirabeau!

Wow... simply... wow! Thank yo so much!

I made the experiment you suggested, and it worked like a charm. I just have to figure out how to make a really small modification to adapt it to my case. In case the "B" column value is 1, it shouldn't insert any rows and the rest should insert the X number - 1 (I am already counting the existing row as one of the rows i want to include).

I really like how elegant and fast your code is. To be honest, I don't understand much of it... it made me realize how much more I have to learn. Just one fast question... when you are declaring variables, what does the "&" symbol means? Does it make it a different type of variable?

Again, you have no idea of how much you have helped me =) Thank you very much

 

[mirabeau]

Hello mirabeau!

Wow... simply... wow! Thank yo so much!

I made the experiment you suggested, and it worked like a charm. I just have to figure out how to make a really small modification to adapt it to my case. In case the "B" column value is 1, it shouldn't insert any rows and the rest should insert the X number - 1 (I am already counting the existing row as one of the rows i want to include).

I really like how elegant and fast your code is. To be honest, I don't understand much of it... it made me realize how much more I have to learn. Just one fast question... when you are declaring variables, what does the "&" symbol means? Does it make it a different type of variable?

Again, you have no idea of how much you have helped me =) Thank you very much

Bear,

The & symbol in this context is just shorthand for long.

That is
Dim n as Long
and
Dim n&
mean the same.

Some coders like the long version as easier to understand. I'm just a lazy typist.

Post back if you want anything further on that code.

 

[Bearlord]

Hello mirabeau, thanks a lot for the & symbol explanation.

I was able to understand (or at least that's what I believe xD) your code. It is very clever indeed.
What I understood was that it creates 2 huge arrays, in the first one it stores a counter of all the rows there should be. In the other array, a boolean array, it stores where a value was found. Then it inserts the entire array in column C and "resizes" it, effectively (almost magically, for me xD) inserting all the rows needed.

I would have never thought of such a clever solution. I think you will be glad to know that today I tested the code in one of the large files and it was outstandingly faster than my previous code. With my previous code it took a little more than one hour to insert all the rows to a single sheet. Your code did it under 10 minutes! Couldn't really tell the real speed since it wasn't the only code running, but still the results were amazing. =)

 

[mirabeau]

Hello mirabeau, thanks a lot for the & symbol explanation.

I was able to understand (or at least that's what I believe xD) your code. It is very clever indeed.
What I understood was that it creates 2 huge arrays, in the first one it stores a counter of all the rows there should be. In the other array, a boolean array, it stores where a value was found. Then it inserts the entire array in column C and "resizes" it, effectively (almost magically, for me xD) inserting all the rows needed.

I would have never thought of such a clever solution. I think you will be glad to know that today I tested the code in one of the large files and it was outstandingly faster than my previous code. With my previous code it took a little more than one hour to insert all the rows to a single sheet. Your code did it under 10 minutes! Couldn't really tell the real speed since it wasn't the only code running, but still the results were amazing. =)

Bear,

Thanks for feedback. Glad that you liked the approach.
But I'd be very disappointed if that code took anywhere near as long as 10 minutes. Lots less than 10 seconds would be more my expectation.

To try to demonstrate that, consider expanding the experiment I suggested earlier.
Assuming you have Excel 2007 or later, run the test data generation code below.
Then run the insert row macro as below. That has a built-in timer telling you how long it takes to execute.

Sub test_data_generation()
Dim rw As Long
rw = 100000
With Cells(1).Resize(rw)
    .Resize(, 5) = "=""R""&row()&"" C""&column()"
    .Offset(, 5) = "=if(rand()<.2,int(rand()*10)+1,"""")"
    .Resize(, 6).Value = .Resize(, 6).Value
End With
End Sub

then

Sub insertrows2()
t = Timer
Const m& = 10 ^ 6
Dim b(m) As Boolean, a As Variant
Dim u&(1 To m, 1 To 1)
Dim rw&, cl&, j&, q&, c&

a = Cells(1).CurrentRegion
rw = UBound(a, 1)
cl = UBound(a, 2)

For j = 1 To rw
    q = q + a(j, cl) + 1
    b(q) = True
    u(j, 1) = q
Next j

For j = 1 To q
    If Not b(j) Then c = c + 1: u(rw + c, 1) = j
Next j

Cells(cl + 1).Resize(q) = u
Cells(1).Resize(q, cl + 1).Sort Cells(cl + 1), Header:=xlNo
Cells(cl + 1).Resize(q).ClearContents
MsgBox Round(Timer - t, 3) & " seconds runtime"
End Sub

 

[Bearlord]

Mirabeau you were absolutely right, the 10 minute mark was due to the other code. I ran the code you told me (Office 2010) and the runtime result was an impressive 1.07 seconds! I must say I'm really impressed. I will have to work a lot to optimise/optimize my codes. I had no idea of how inefficient my coding skills were, I hope I can learn to make fast code as yours someday ^^

Thank you very much again Mirabeau!

 

[mirabeau]

Mirabeau you were absolutely right, the 10 minute mark was due to the other code. I ran the code you told me (Office 2010) and the runtime result was an impressive 1.07 seconds! I must say I'm really impressed. I will have to work a lot to optimise/optimize my codes. I had no idea of how inefficient my coding skills were, I hope I can learn to make fast code as yours someday ^^

Thank you very much again Mirabeau!

Thanks for the feedback. Glad to be of help.