RobJanine
asked on
To make code work faster
Hi again experts,
I have some code that delete's rows if the data in that row is not between two date's.
What I need to do is make the code work faster. At the moment it seams to be looking at each row then deleteing that row then moving onto the next row. Is there a way of seleting all the rows not within the date range and then deleteing all those rows at once.
There is another sheet that has formulas based on the data in this sheet and that is why it is going slow I think. I have make the formulas in the other sheet as streamlined as posible with help from here already.
Present code:
Sub Del_button()
Dim dt_fr As Date
Dim dt_to As Date
Dim dt As Date
ActiveSheet.Range("C5").Se lect
dt_fr = ActiveSheet.Range("I1").Va lue
dt_to = ActiveSheet.Range("I2").Va lue + 1
Do
dt = ActiveCell.Value
If dt < dt_fr Or dt > dt_to Then
ActiveCell.EntireRow.Delet e
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = ""
ActiveSheet.Range("D5:G" & ActiveSheet.UsedRange.Rows .Count).Cl ear
ActiveSheet.Range("B5:C" & ActiveSheet.UsedRange.Rows .Count).So rt _
Key1:=ActiveSheet.Columns( "B"), _
Key2:=ActiveSheet.Columns( "C")
With Sheets("FP Reader")
.Range("B5:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Advanc edFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("Emplo yees - Table 1").Range("C3"), Unique:=True
End With
With Sheets("Employees - Table 1")
If .Range("C3") = .Range("C4") Then
.Range("C3").Delete
End If
End With
ActiveSheet.Range("I4").Se lect
End Sub
Cheers
Rob.
I have some code that delete's rows if the data in that row is not between two date's.
What I need to do is make the code work faster. At the moment it seams to be looking at each row then deleteing that row then moving onto the next row. Is there a way of seleting all the rows not within the date range and then deleteing all those rows at once.
There is another sheet that has formulas based on the data in this sheet and that is why it is going slow I think. I have make the formulas in the other sheet as streamlined as posible with help from here already.
Present code:
Sub Del_button()
Dim dt_fr As Date
Dim dt_to As Date
Dim dt As Date
ActiveSheet.Range("C5").Se
dt_fr = ActiveSheet.Range("I1").Va
dt_to = ActiveSheet.Range("I2").Va
Do
dt = ActiveCell.Value
If dt < dt_fr Or dt > dt_to Then
ActiveCell.EntireRow.Delet
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = ""
ActiveSheet.Range("D5:G" & ActiveSheet.UsedRange.Rows
ActiveSheet.Range("B5:C" & ActiveSheet.UsedRange.Rows
Key1:=ActiveSheet.Columns(
Key2:=ActiveSheet.Columns(
With Sheets("FP Reader")
.Range("B5:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Advanc
CopyToRange:=Sheets("Emplo
End With
With Sheets("Employees - Table 1")
If .Range("C3") = .Range("C4") Then
.Range("C3").Delete
End If
End With
ActiveSheet.Range("I4").Se
End Sub
Cheers
Rob.
ASKER
It took 6 secs instead of 24 secs....way better thankyou.
no other suggestions?, or is this about the best I can hope for?
Thanks
Rob
no other suggestions?, or is this about the best I can hope for?
Thanks
Rob
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Set delrng = Union(delrng, ActiveCell)
it said 'Invalid procedure call or argument'
any ideas
it said 'Invalid procedure call or argument'
any ideas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks alot....1.2 secs now to complete task.
credit to both....
thanks again for both input to get final result.
Rob.
credit to both....
thanks again for both input to get final result.
Rob.
ASKER
thanks again
Please try this and let us know if it helps...
Sub Del()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim dt_fr As Date
Dim dt_to As Date
Dim dt As Date
'Your code goes here
'+++++++++++++++++++++++++
'+++++++++++++++++++++++++
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating
Application.Calculation = xlCalculationAutomatic
End Sub
Thanks...