Code Improvements
On 27/09/2012 15:42, Don Guillett wrote:
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
Wow! Thanks Don.
Never even thought to use AutoFilter that way.
Can't believe how quick that runs.
Thanks very much.
|