Simple VBA code freezes/crashes Excel

[ubergreen]

I am using the VBA code below to delete a specific number of rows each time a button is pressed, which we can call Workbook2

Sub DeleteRows()

For y = 356 To 2 Step -1
Rows(y).Delete
Next y
    
End Sub

I have another workbook, Workbook1, that references the values in Workbook2. Workbook1 has various references to other additional workbooks and functions/formulas to calculate everything.

When I execute this code, I will see this popping up at the bottom and it'll flash really quickly values between 0-100%

That's the only activity I see in my Excel sheets once the delete rows VBA code is executed.

If I manually delete the rows from Workbook2, everything will work properly and nothing crashes or freezes. All of the values from Workbook2 will be correctly referenced into Workbook1 accordingly. Additionally, if I run the VBA code with only Workbook2 open, the VBA code will perform perfectly without any issues as well.

This makes me a bit confused as to exactly what could be causing the my Excel to freeze when both workbooks are open and I execute the function.

 

[DanteAmor]

Putting the deletion of rows in a cycle will always be slow, and even more so if you have formulas.

What is recommended in your case if you are always going to delete that range of rows:

Sub deleterows_2()
  Rows("2:356").Delete
End Sub

 

[ubergreen]

Putting the deletion of rows in a cycle will always be slow, and even more so if you have formulas.

What is recommended in your case if you are always going to delete that range of rows:

Sub deleterows_2()
  Rows("2:356").Delete
End Sub

Thanks. That worked perfectly. I had found the original code online, but I guess I didn't understand it enough to know that was not an efficient or maybe ideal way of deleting rows.

Now it makes me wonder if some of my other codes/functions could be optimized to allow things to run faster and more efficiently.