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

uSub FilterByDateTime()
Dim sd As Double

If IsDate(Range("r1")) Then
sd = Range("s1") '+14 = 2pm
sdDate = DateSerial(Year(sd), Month(sd), Day(sd)) + _
TimeSerial(Hour(sd), Minute(sd), Second(sd))

With ActiveSheet.UsedRange
.AutoFilter Field:=16, Criteria1:="<=" & sd _
, Operator:=xlOr, Criteria2:="" & sd + 1 '+ 1 day
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End If
End Subse this