Excel macros extremely slow

[Shloime]

I have some macros in an excel file and they became extremely slow,
I added a few lines debug.print now to see where it is taking long,
I use this to find the last row/coplumn:
Range("o:v").Find(What:="*", _ after:=Range("o1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row.
I found that is taking 2 to 3 seconds,
it is was better when i changed it to:
Range("o1:v1111").Find(What:="*", _ after:=Range("o1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row.
and maybe even
Range("o1:v" & rows.count).Find(What:="*", _ after:=Range("o1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row.
does it make sense Range("o1:v" & rows.count) to be more efficient than Range("o:v")?

I also have another macro to save or print data in a few columns, and this one started to slow down everything till i close the file,
this is the full macro to save
Sub PageSetupAndSave()
Dim svrg As Range
Dim Fold As String

finrw = stmn.Range("o:v").Find(What:="*", after:=Range("o1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).row
Application.ScreenUpdating = False '
Range("5:5").RowHeight = 36.75
Range("p").ColumnWidth = 17
Range("q:q").ColumnWidth = 17
Range("r:r").ColumnWidth = 9
Range("s:s").ColumnWidth = 9
Range("t:t").ColumnWidth = 10.35
Range("u:u").ColumnWidth = 25


Set svrg = Range("p1:u" & finrw + 1)
svrg.Select


With ActiveSheet.PageSetup

.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)


'.PrintQuality = 600

.Orientation = xlPortrait

.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False

.FitToPagesWide = 1
.FitToPagesTall = 1

End With

Dim Folder As String
Folder = ThisWorkbook.Path & "\" & Format(Now, "yyyymmddhh") & "\"
Fold = Dir(Folder, vbDirectory)
If Fold = "" Then MkDir Folder
'folder = ThisWorkbook.Path & "\" & Format(Now, "yyyymmddhhmmss") ' ThisWorkbook.Path & "\Statements02"
FilNM = Replace(Folder & Range("b8") & Chr(32) & Format(Now, "yymmddhhmm") & Chr(32) & Range("b1").Value & ".pdf", """", "")


Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FilNM, OpenAfterPublish:=False

Application.ScreenUpdating = True

End Sub

 

[Jeffrey Mahoney]

In a brand new workbook I populated some cells down to row 1048576. The most time that it took was .0039 seconds.

 

[Shloime]

Hi, first what do you use to time in milliseconds?
do you have any idea what may make it slow in my file?

 

[Jeffrey Mahoney]

Timer can provide fractional portions of a second. Even 1 second to do a search seems long. Large workbooks contribute

Try adding this to the top of your macro
Application.Calculation = xlCalculationManual

and then this at the end
Application.Calculation =xlCalculationAutomatic

To test the speed
Dim t as single

t = timer
. . . . . run the code

debug.print timer - t

 

[Shloime]

I found that

Range("o1:v10000").Find(What:="*", _ after:=Range("o1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row

would be faster than

Range("o:v").Find(What:="*", _ after:=Range("o1"), _ LookAt:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row

Columns("p").ColumnWidth

faster than

Range("p:p").ColumnWidth

,

But the main issue with my file was the page setup, cancelling it would make it much faster,
I'm not very sure the right way to reset the page layout, If anyone can suggest full reset to page layout setting for printing.
Also after reset I'd like not to see any page breaks.