On Tuesday, September 25, 2012 5:02:45 PM UTC-5, milli wrote:
Hi all.

I need a way to improve the code shown below.

The code first checks if time in column P is between the namedranges

ShipmentDate_StartValue & ShipmentDate_EndValue, marks the row del or keep.

Then loops back through this and deletes cols A through Q if marked del.



Sheet3.Activate

Range("Q2").Select

' Removes extra rows not within start & end range

Do While Not IsEmpty(ActiveCell.Offset(0, -1))

ActiveCell.FormulaR1C1 = _

"=IF(OR('Dollies -

Shipment'!RC[-1]<ShipmentDate_StartValue,'Dollies -

Shipment'!RC[-1]ShipmentDate_EndValue),""Del"",""Keep"")"

ActiveCell.Offset(1, 0).Select

Loop

Range("Q2").Select

Do While Not IsEmpty(ActiveCell.Offset(0, -1))

Do While ActiveCell = "Del"

ActiveCell.Offset(0, -16).Range("A1:Q1").Select

Selection.Delete Shift:=xlUp

ActiveCell.Offset(0, 16).Select

Loop

ActiveCell.Offset(1, 0).Select

Loop

ActiveSheet.Range("Q:Q").ClearContents





When I run this using Excel 2003 the code takes approx. 8/9 seconds.

When using Excel 2010 the code runs for nearly 2 minutes.

I cannot see any reason for this delay.



Is there a more efficient way of coding the above.

Our company has now upgraded to Excel 2007 which is the reason for the

change.

Thanks in advance,

edul


Copy this. If not OK send file to dguillett @ gmail.com with a complete explanation and both codes.

Sub DeleteRangeIf_SAS()
Dim i As Long
Application.ScreenUpdating = False
With Sheet3 ' works from anywhere in the workbook
For i = 2 To .Cells(Rows.Count, "p").End(xlUp).Row
If .Cells(i, "Q") = ShipmentDate_StartValue And _
.Cells(i, "Q") < ShipmentDate_endvalue Then _
..Range(Cells(i, "a"), Cells(i, "q")).Delete Shift:=xlUp
Next i
Application.ScreenUpdating = True
End With
columns("q").clear 'question if necessary?
End Sub