Excel VBA - slow scroll bar

[fbermudez]

I created a macro in which I had to set the print settings using:

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
ActiveSheet.PageSetup.PrintArea = "$A:$U"

End users complained that after the macro finishes, the Vertical scroll bar is slow, and in fact after the macro finishes, if you click on the vertical scroll bar and move it up and down quickly, one can clearly see a lag or delay between the pointer and the scroll bar.

Any ideas as to why this is happening and how I can fix it?

thank you

 

[BiggerBrother]

Is that most of the code from the macro. If so, you should close the with statement. I'm not sure if you have missed it, or just not posted it here. It might just be that simple. What is the code for the rest of the macro?

BB

 

[fbermudez]

below is entire code

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A:$U"
'ActiveSheet.CenterHeader.Paste
With ActiveSheet.PageSetup

Range("A1").Select
'.LeftHeader = ""
.CenterHeader = "&""Arial,Bold""&12A/R by Collector as of " & FormatDateTime(Range("V1"), 2)

'.RightHeader = ""
'.LeftFooter = ""
.CenterFooter = "&P"
.RightFooter = Now()
.LeftMargin = Application.InchesToPoints(0.166)
.RightMargin = Application.InchesToPoints(0.166)
.TopMargin = Application.InchesToPoints(0.4166)
.BottomMargin = Application.InchesToPoints(0.52)
'.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.19)
.PrintHeadings = False
.PrintGridlines = True
'.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
'.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
'.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 87
'.PrintErrors = xlPrintErrorsDisplayed
End With

 

[KenWright]

The answer is in your first post - You are setting the print area to A:U. Well A:U is 65,536 rows long, so instantly the scrollbar represents all of those rows.

You should be setting the print area to a smaller area by defining the rows to be included, either by hardcoding it in if it doesn't change, or by working out through code what the last row is.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Here's an example of how you would do that:-

The lrow calc determines the last used row in the spreadsheet, and then the PageSetup range simply makes use of that value as the last row in setting the range:-

Sub Printme()

Dim lrow As Long

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
ActiveSheet.PageSetup.PrintArea = Range("$A$1:$U$" & lrow)

End Sub

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------