Slow Macro

70sgirl

New Member
Joined
Oct 23, 2002
Messages
18
I wrote a macro to test inserting a blank row after rows containing certain items. I tested this alone and it went fairly fast for the computer I used it on (an older computer that you can see every move on). I then added the code for this to the end of a larger macro that is run. The time it takes to run just the insert rows part (after the rest of the macro is completed) is much longer than the time it took to run the same code when it was in its own macro.

Why does it get slowed down when placed after other code? Instead of one large macro would it be better to write a bunch of smaller macros that get called by one common macro?

Thanks!!!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi

I am not a code person, but it seems that you might tell us what the other macro is doing or supply some code to see if the two might be conflicting with each other.

pll
 

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Well, I don't know exactly what the difference is between both machines. But to speed things up, you may want to toss this line at the top of your code (if you don't already have it).

Application.ScreenUpdating = False

HTH
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
First check that you have

Application.ScreenUpdating = False

this will quicken things up a lot.

Post your code and maybe we can take a look - if your other part of the macro is doing lots of calculations it would be best to set

Application.Calculation = xlManual

And then calculate sheets individually as and when required.
 

70sgirl

New Member
Joined
Oct 23, 2002
Messages
18

ADVERTISEMENT

The large macro copies in a sheet and deletes the old sheet that was there (a weekly report pulled off of one of our customers websites). Then it basically formats the spreadsheet the way my boss wants it. It resizes columns, moves them around, sets the page setup and other print settings. It also adds a column to total the quantities by each part number. The last part is the macro I tested seperately, which adds a blank row to seperate different part numbers (column D).

I tried to insert the row two ways with not a huge time difference between the two.

Sub addrows()

finalrow = Range("A9999").End(xlUp).Row
Range("H1") = finalrow
For i = 8 To finalrow
If (Range("D" & i) <> Range("D" & (i - 1))) Then
Rows(i).Select
Selection.insert shift:=x1down
i = i + 1
finalrow = finalrow + 1
End If
Next i
End Sub


or

Sub doloop()

finalrow = Range("A9999").End(xlUp).Row
i = 8
Do While i < finalrow
If (Range("D" & i) <> Range("D" & (i - 1))) Then
Rows(i).Select
Selection.insert shift:=x1down
i = i + 2
finalrow = Range("A9999").End(xlUp).Row
Else: i = i + 1
End If
Loop
End Sub


I will try Application.ScreenUpdating = False (I can't get to that computer until that person leaves in about half an hour). It all works fine on my computer, but her computer is a lot slower than mine. Just the adding rows part of the macro takes 3-4 minutes on her computer for a spreadsheet that ends up being over 1000 rows after the rows are inserted.


Thanks for the help!
 

charlie2

New Member
Joined
Oct 23, 2002
Messages
1
I would guess that your default printer is the problem. If your default printer uses your PC's memory, rather than having it's own, it makes a huge difference in the speed.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Don't forget to reset the screen updating bach on at the end of your code! If you have turned it off at the top of your code. JSW
 

70sgirl

New Member
Joined
Oct 23, 2002
Messages
18
Thanks for the info. I turned screenupdating to false at the beginning of the macro and to true at the end and it shaved about a minute off the total time for the macro (down to about 5 minutes now).

I still do not understand why a section of the macro would take a different amount of time to run if it's run in its own macro vs. being run at the end of another macro.

Here's the rest of the macro:

Sub Format()

Application.ScreenUpdating = False
Workbooks.Open FileName:="C:My Documents36wkreport.xls"
Sheets("36wkreport").Select
Sheets("36wkreport").Copy Before:=Workbooks("B07Plan.xls").Sheets(1)
Sheets("36wkreport").Select
ActiveWindow.SelectedSheets.Delete
Sheets("36wkreport (2)").Select
Sheets("36wkreport (2)").Name = "36wkreport"
Windows("36wkreport.xls").Activate
ActiveWindow.Close
Columns("A:A").ColumnWidth = 4
Columns("B:B").ColumnWidth = 4.29
Columns("C:C").ColumnWidth = 5.43
Columns("D:D").Select
Selection.Columns.AutoFit
Columns("E:E").ColumnWidth = 3.86
Columns("F:F").Select
Selection.Columns.AutoFit
Columns("G:G").ColumnWidth = 4.29
Columns("H:H").ColumnWidth = 4.29
Columns("I:I").Select
Selection.EntireColumn.Hidden = True
Columns("J:J").ColumnWidth = 4.29
Columns("K:K").ColumnWidth = 3.57
Columns("L:L").ColumnWidth = 3.86
Columns("M:M").Select
Selection.Columns.AutoFit
Columns("N:N").ColumnWidth = 5.29
ActiveWindow.SmallScroll ToRight:=9
Columns("O:O").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Columns("Q:Q").Select
Selection.Columns.AutoFit
Columns("R:S").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.LargeScroll ToRight:=-1
Cells.Select


With ActiveSheet.PageSetup
.PrintTitleRows = "$6:$6"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.printarea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = "&D"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With

finalrow = Range("A9999").End(xlUp).Row

Columns("J:J").Select
Selection.Insert shift:=xlToRight
Range("J6").Select
ActiveCell.FormulaR1C1 = "Total"
Range("J7").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]=R[-1]C[-6],R[-1]C+RC[-2],RC[-2])"
Columns("J:J").Select
Selection.NumberFormat = "0"
Range("J7").Select
Selection.Copy
Range("J7:J" & finalrow).Select
ActiveSheet.Paste
Selection.Font.ColorIndex = 5
Columns("J:J").Select
Selection.ColumnWidth = 5.57
Range("J5").Select

ActiveWindow.ScrollRow = 1
ActiveWindow.LargeScroll ToRight:=-1
Columns("D:D").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight
Columns("H:H").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert shift:=xlToRight
Columns("J:J").Select
Selection.Cut
Columns("D:D").Select
Selection.Insert shift:=xlToRight
Columns("J:J").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert shift:=xlToRight
Columns("I:I").Select
Selection.Cut
Columns("F:F").Select
Selection.Insert shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=7
Columns("Q:Q").Select
Selection.Cut
ActiveWindow.LargeScroll ToRight:=-1
Columns("G:G").Select
Selection.Insert shift:=xlToRight


For i = 8 To 9999
If (Range("C" & i)) Then
If (Range("B" & i) <> Range("B" & (i - 1))) Then
Rows(i).Select
Selection.Insert shift:=xlDown
i = i + 1
End If
End If
Next i

Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub


Thanks!
 

Forum statistics

Threads
1,144,274
Messages
5,723,444
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top