Slow sorts on Excel

[ZugZug]

I have a spreadsheet that I created a custom macro to sort certain
columns in a workbook. For some reason after I have the workbook open
for any extended period of time (saving and updating periodically) the
sort will go from taking about 1.5 seconds to about 10 or 15 seconds.
Once it takes 10 or 15 seconds I have to close the spreadsheet down
and re-open to get back to the 1.5 second sort (I don't have to close
Excel).

Does anyone know why this may be hapening? I know it's ugly code and
can be cleaned up...my main question is what could be causing the slow
down.

Thanks,
Ryan

Here is some of the code (sort table/color columns/hide or display
values):

----------------------------------------------------------------
Worksheets("Due Dates").Activate

blah = ThisWorkbook.calc_rows()

Range(Cells(start_row, Module1.start_Col),
Cells(Module1.num_of_rows,Module1.end_Col)).Select

Selection.Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

current_row = Module1.start_row

While current_row <= Module1.num_of_rows

Worksheets("Due Dates").Activate
Rows(current_row).RowHeight = 12.75
Range(Cells(current_row, Module1.start_Col), Cells(current_row,
Module1.end_Col)).Select

If Cells(current_row, done_row).Value = "x" Or Cells(current_row,
done_row).Value = "X" Or Cells(current_row, ignore_row).Value = "x" Or
Cells(current_row, ignore_row).Value = "X" Then
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Rows(current_row).Select
Selection.EntireRow.Hidden = True

ElseIf Cells(current_row, hot_row).Value = "x" Or
Cells(current_row, hot_row).Value = "X" Then
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
ElseIf Cells(current_row, new_dt_row).Value > 0 And
Cells(current_row, new_dt_row).Value < Date + 2 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
ElseIf Cells(current_row, new_dt_row).Value = 0 And
Cells(current_row, date_row).Value < Date + 2 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Else
Selection.Interior.ColorIndex = xlNone
End If

current_row = current_row + 1
Wend

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

 

[Dave Peterson]

You could speed it up by removing the .selects and .activates and just working
directly against the ranges.

And turning off screenupdating and turning calculation to manual will improve
the speed, too. (Turn them back when you're done.)

But my guess is that since you're hiding rows, excel wants to determine where
those little dotted lines that show where pagebreaks go.

You see them after a File|print preview.

Next time your macro slows down, do Tools|options|view Tab|
and uncheck "page breaks"

Then try your code.

If it sped up, you could add the equivalent in code:

ActiveSheet.DisplayPageBreaks = False

Or once you remove the .activates:

Worksheets("Due Dates").DisplayPageBreaks = False

 

[ZugZug]

Dave,

Thanks for the quick follow-up. Great guess on the print preview
suggestion. I tried your suggestion when the slow down occured and it
fixed the problem.

Now I can see that once I print something...the dotted line
appears...and the slow down occurs.

Thanks again,
Ryan