Hi Mike, Can you notice and send the back the change in Memory Usage by Excel application before and after running the macro? You can locate it in Task manager. You can try clearing clipboard(Application.CutCopyMode = False) to experiment harmlessly.
________________________________________ Thanks & Regards Ashish Jain McKinsey India Knowledge Center (Microsoft Certified Application Specialist) (Microsoft Certified Professional) http://www.excelitems.com http://www.openexcel.com ________________________________________ On Nov 1, 9:37 pm, Mike Magill <mike.mag...@gmail.com> wrote: > Hi, > > I have a rather long macro that controls and limits the printing > process for the end user. It works okay but after running and you > return to the spreadsheet the screen is very slow to refresh as you > scroll around the worksheet. > > The macro gives the user the choice of viewing a filtered set of > results on screen (Print_View_Option = "V") or printing out the result > (Print_View_Option = "V"). I think I've worked out that the issue > only occurs when the user tries to print out the results and the macro > adjusts the Page Setup. > > I've done some research that suggests the Page Setup routine is > inherently slow but this doesn't explain the slowness AFTER the macro > has finished. > > I've attached a slightly simplified version of the macro below that > still manifests the same problem. > > Can anyone help? > > Sub Print_Options2() > > Dim RR As Object > Dim LastRow As Long > Dim ReportOrder As String > Dim OverallFilterType As String > Dim IndividualFilterType As String > Dim PaperSize As String > Dim ReportType As String > > Set RR = ThisWorkbook.Sheets("Risk Register") > > Print_View_Option = "P" > PaperSize = "A4" > ReportType = "Full Risk Register" > OverallFilterType = "A" > IndividualFilterType = "All Individual Control Assessments" > > Application.ScreenUpdating = False > Application.EnableEvents = False > > RR.Unprotect Password:=Password > > On Error Resume Next > RR.ShowAllData > On Error GoTo 0 > > ' Ensures any rows with wrapped text are expanded so that all text > is visible > LastRow = RR.Range("AD" & Rows.Count).End(xlUp).Row > RR.Rows("6:" & LastRow).EntireRow.AutoFit > > ' Hide rows with no data > Selection.AutoFilter Field:=30, Criteria1:="x" > > If Application.Dialogs(xlDialogPrinterSetup).Show Then > RR.DisplayPageBreaks = False > With RR.PageSetup > If PaperSize = "A3" Then > .PaperSize = xlPaperA3 > Else > .PaperSize = xlPaperA4 > End If > .PrintArea = "$B:$AB" > .LeftFooter = _ > "&""Arial,Bold""Print Criteria:&""Arial,Regular""" & > Chr(10) & _ > " - " & ReportType & Chr(10) & " - " & > OverallFilterType & Chr(10) & _ > " - " & IndividualFilterType > .RightFooter = RR.Range("K1").Value > End With > ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True > End If > > ' Show rows with no data > Selection.AutoFilter Field:=30 > > ' When user has chosen to Print rather than View revert all > settings back to standard > If Print_View_Option = "P" Then > ' Revert hidden columns to original state > If RR.Range("J2") = "Consolidation" Then > RR.Columns("A:A").EntireColumn.Hidden = True > RR.Columns("B:B").EntireColumn.Hidden = False > Else > RR.Columns("A:B").EntireColumn.Hidden = True > End If > RR.Columns("C:P").EntireColumn.Hidden = False > RR.Columns("Q:R").EntireColumn.Hidden = True > RR.Columns("S:V").EntireColumn.Hidden = False > RR.Columns("X:AB").EntireColumn.Hidden = False > RR.Columns("AC:CD").EntireColumn.Hidden = True > > On Error Resume Next > RR.ShowAllData > On Error GoTo 0 > > End If > > RR.Range("I3").Activate > RR.Protect Password:=Password, DrawingObjects:=True, > Contents:=True, Scenarios:=True > Application.ScreenUpdating = True > Application.EnableEvents = True > > Set RR = Nothing > > End Sub -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
