Slow Macro

[70sgirl]

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!!!

 

[plettieri]

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, 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]

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]

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]

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]

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]

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!