Adding empty lines based on value via “I” loop which crashes and lags

[LostandAngryatVBA]

Hello all,

I have been stuck 5 hours on this topic. Please help.

I have a code as below. First part is to add one empty line. Next part is an “i” loop where It is adding empty lines based on value of certain cell. This code lags my macro so bad, like 2-3min when I run it. It even crash. The ironic thing is I have 2 subs branch out and when branch 1 run this code it works fine vs when it enters another branch, it lag/crash. Therefore I know the codes work but I’m confused why one sub works fast and one die. Regardless, I’m looking to improve my codes so it doesn’t lag/crash



Application.CutCopyMode = False
Application.ScreenUpdating = False
Sheets("worksheet1").Select



Range("F2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove



Supply = Range("AO189")
Dim i As Integer
Dim numdx As Integer
numdx = Range("t219") + 4



Range("F1").Select
ActiveCell.Offset(Tier + 7).Select ' space in inc note
For i = 1 To numdx
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next i

 

[Joe4]

Welcome to the Board!

You really should try to limit the use of "Select" and "Selection" in your code (especially within loops!) as it can really slow your code down.
Also, you appear to be using a variable that I do not see defined or set anywhere ("Tier").
Where does that come from?

I think your code can probably be written to be simplified. However, it would be much more helpful for us to see what your data looks like, and to understand exactly what you are trying to do (explain in plain English, instead of us trying to figure it from some confusing code).

Can you post some sample data and explain exactly what you want this code to do?

 

[LostandAngryatVBA]

Welcome to the Board!

You really should try to limit the use of "Select" and "Selection" in your code (especially within loops!) as it can really slow your code down.
Also, you appear to be using a variable that I do not see defined or set anywhere ("Tier").
Where does that come from?

I think your code can probably be written to be simplified. However, it would be much more helpful for us to see what your data looks like, and to understand exactly what you are trying to do (explain in plain English, instead of us trying to figure it from some confusing code).

Can you post some sample data and explain exactly what you want this code to do?

hello, thank you for replying.

sorry let me do the code again.

Application.CutCopyMode = False
Application.ScreenUpdating = False
Sheets("worksheet1").Select



Range("F2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ---> this is to add 1 row under F2 (super laggy)



Tier = Range("AO189") ---> this number fluctuate based on the situation
Dim i As Integer
Dim numdx As Integer
numdx = Range("t219") + 4 ---> t219 fluctuate based on the situation too



Range("F1").Select ---> this entire part add # of lines in based on the "Tier" vaslue.
ActiveCell.Offset(Tier + 7).Select
For i = 1 To numdx
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next i


---> the goal of this part is to add in appropriate number of lines so that i can copy/tranpose certain sentence into these "spaces". Thank you again.

 

[Joe4]

OK, you don't need loops at all in your procedure (and it is always a good idea to avoid loops whenever possible, as loops are very inefficient and slow your code down).

Try this:

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Sheets("worksheet1").Select
    Range("F2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Dim Tier As Long
    Tier = Range("AO189")

    Dim numdx As Integer
    numdx = Range("t219") + 4

    Range(Cells(8 + Tier, "F"), Cells(7 + Tier + numdx, "F")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

 

[LostandAngryatVBA]

OK, you don't need loops at all in your procedure (and it is always a good idea to avoid loops whenever possible, as loops are very inefficient and slow your code down).

Try this:

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    Sheets("worksheet1").Select
    Range("F2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Dim Tier As Long
    Tier = Range("AO189")

    Dim numdx As Integer
    numdx = Range("t219") + 4

    Range(Cells(8 + Tier, "F"), Cells(7 + Tier + numdx, "F")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

 

[LostandAngryatVBA]

OK, you don't need loops at all in your procedure (and it is always a good idea to avoid loops whenever possible, as loops are very inefficient and slow your code down).

Try this:

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    Sheets("worksheet1").Select
    Range("F2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Dim Tier As Long
    Tier = Range("AO189")

    Dim numdx As Integer
    numdx = Range("t219") + 4

    Range(Cells(8 + Tier, "F"), Cells(7 + Tier + numdx, "F")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

thank you for your help. I'm very grateful. This code does work and doesn't crash so YAY (compare to mine). Is there any way for it be faster(still lag) like 1-4min?

 

[Joe4]

There is nothing in that code that would cause it to run that long. The code is actually very simple. It runs on my computer in less than 1 second!
If it is running slow, it is probably due to something else, like if you have huge amounts of data in your file, or you have external links in the file, etc.

 

[LostandAngryatVBA]

OK, you don't need loops at all in your procedure (and it is always a good idea to avoid loops whenever possible, as loops are very inefficient and slow your code down).

Try this:

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
  
    Sheets("worksheet1").Select
    Range("F2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Dim Tier As Long
    Tier = Range("AO189")

    Dim numdx As Integer
    numdx = Range("t219") + 4

    Range(Cells(8 + Tier, "F"), Cells(7 + Tier + numdx, "F")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

Also, I did a code that is much

There is nothing in that code that would cause it to run that long. The code is actually very simple. It runs on my computer in less than 1 second!
If it is running slow, it is probably due to something else, like if you have huge amounts of data in your file, or you have external links in the file, etc.

this sub is the last sub in my macro so i'm thinking it is a lot of data. do you have any idea to fix this? thank you

 

[Joe4]

this sub is the last sub in my macro so i'm thinking it is a lot of data. do you have any idea to fix this?

You have other code in your workbook?
Are you sure that it is not that other code that is slowing things down?

If you place this code at the top of every VBA procedure you have (just after the "Sub ..." line):

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

and place this code just before the "End Sub" line of each procedure:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

it may help to speed things up.

Of course, your other code may be able to be optimized too, by removing "Select" and "Selection" and loops wherever possible.

 

[LostandAngryatVBA]

You have other code in your workbook?
Are you sure that it is not that other code that is slowing things down?

If you place this code at the top of every VBA procedure you have (just after the "Sub ..." line):

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False

and place this code just before the "End Sub" line of each procedure:

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True

it may help to speed things up.

Of course, your other code may be able to be optimized too, by removing "Select" and "Selection" and loops wherever possible.

Thank you Joe for taking the time to answer my question. It ends up being my excel file is corrupted. i went back to 10 versions earlier and it works fine. ima marked this as solved!