Hello, menajaro ( and everyone )
This is a very minor point, and possibly just academic.
If I am not mistaken it seems you are using Application Evaluate in your original coding. I have heard it said , ( for example
https://fastexcel.wordpress.com/2011/11 ... e-as-fast/ ) that a Worksheet’s Evaluate is faster. I don’t have any personal experience to confirm that, and I don’t have the time just now to do extensive tests on your data.
But maybe you should consider it, and test that issue, with some performance checks, as p45 suggests. Remember that speed performance can be an inconstant thing. You should carefully measure speed comparisons and importantly repeat them and make average results. Otherwise you will be going around in circles chasing your tail as a few quick measurements wont tell you anything of the typical likely results. We have discussed this in previous Threads, I think.
Here some macros only intended to demonstrate the
Application Evaluate versus a Worksheet’s Evaluate issue
In this following macro I have a version of your coding from post 1, which is just changed a little for two reasons
_ just as I prefer to lay it out
, and also
_ I included the speed measurement things as p45 suggested.
Code: Select all
Option Explicit
Sub testitOriginal() ' http://www.eileenslounge.com/viewtopic.php?p=304098#p304098
Dim Eh As Variant, Lr As Long, Interval1 As Double, Interval2 As Double, Interval3 As Double, Interval4, StartTime As Double ' Lr&
Let StartTime = Timer
' letApplication.ScreenUpdating = False
With Worksheets("Sheet1")
Let Lr = .Cells(Rows.Count, "R").End(xlUp).Row
Let Eh = .Range("R7:R" & Lr).Address(external:=True) ' External:=True possibly is needed as code is probably using Applictaion Evaluate. ( without a full range reference it might use the wrong worksheet data, hence the full range referrence is used rather than the simple address referrence)
Let Eh = Evaluate("If(" & Eh & "<=" & CLng(Date - Day(Date)) & ", Row(" & Eh & "))")
Let Eh = Filter(Application.Transpose(Eh), False, False)
Let Interval1 = Timer
Debug.Print "Evaluation time = " & Interval1 - StartTime
If UBound(Eh) = -1 Then Exit Sub
Let Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Resize(1 + UBound(Eh), 12).Value = Application.Index(.Range("a1:V" & Lr), Application.Transpose(Eh), Array(1, 3, 4, 5, 6, 7, 17, 18, 19, 20, 21, 22))
Let Interval2 = Timer
Debug.Print "Copying time = " & Interval2 - Interval1
.Range("A" & Join(Eh, ",A")).EntireRow.Delete
Let Interval3 = Timer
Debug.Print "Deletion time = " & Interval3 - Interval2
End With
With Worksheets("Sheet2")
Let Lr = .Cells(Rows.Count, "H").End(xlUp).Row
.Range("A7:L" & Lr).Sort .Range("D7"), 1, key2:=.Range("F7"), order2:=1, Header:=xlYes
Let Interval4 = Timer
Debug.Print "Sorting time = " & Interval4 - Interval3
' Let Application.ScreenUpdating = True
Debug.Print
End With
End Sub
This next macro is very similar to that above, its just using Worksheets("Sheet1")’s Evaluate instead.
Code: Select all
Sub testit2WorksheetsEvaluate() ' http://www.eileenslounge.com/viewtopic.php?p=304098#p304098
Dim Eh As Variant, Lr As Long, Interval1 As Double, Interval2 As Double, Interval3 As Double, Interval4, StartTime As Double ' Lr&
Let StartTime = Timer
' letApplication.ScreenUpdating = False
With Worksheets("Sheet1")
Let Lr = .Cells(Rows.Count, "R").End(xlUp).Row
Let Eh = .Range("R7:R" & Lr).Address
Let Eh = .Evaluate("If(" & Eh & "<=" & CLng(Date - Day(Date)) & ", Row(" & Eh & "))")
Let Eh = Filter(Application.Transpose(Eh), False, False)
Let Interval1 = Timer
Debug.Print "Evaluation time = " & Interval1 - StartTime
If UBound(Eh) = -1 Then Exit Sub
Let Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Resize(1 + UBound(Eh), 12).Value = Application.Index(.Range("a1:V" & Lr), Application.Transpose(Eh), Array(1, 3, 4, 5, 6, 7, 17, 18, 19, 20, 21, 22))
Let Interval2 = Timer
Debug.Print "Copying time = " & Interval2 - Interval1
.Range("A" & Join(Eh, ",A")).EntireRow.Delete
Let Interval3 = Timer
Debug.Print "Deletion time = " & Interval3 - Interval2
End With
With Worksheets("Sheet2")
Let Lr = .Cells(Rows.Count, "H").End(xlUp).Row
.Range("A7:L" & Lr).Sort .Range("D7"), 1, key2:=.Range("F7"), order2:=1, Header:=xlYes
Let Interval4 = Timer
Debug.Print "Sorting time = " & Interval4 - Interval3
' Let Application.ScreenUpdating = True
Debug.Print
End With
End Sub
'
Finally, just for convenience, I have a simple macro to put things back as they were when you are doing speed measurement comparisons. (In my attached file, I have copied your original test data to the extra 2 worksheets that this macro copies from)
Code: Select all
Sub Startagenon()
Worksheets("Sheet1Original").Cells.Copy
Worksheets("Sheet1").Select
Worksheets("Sheet1").Paste , Destination:=Worksheets("Sheet1").Range("A1")
Worksheets("Sheet2Original").Cells.Copy
Worksheets("Sheet2").Select
Worksheets("Sheet2").Paste , Destination:=Worksheets("Sheet2").Range("A1")
End Sub
_.___
The results I got, don’t really tell us anything as I have only tried on your very small test data in the first workbook you uploaded .
I am only intending to demonstrate the Application Evaluate versus a Worksheet’s Evaluate issue here, I think….
That is all I have time for now. But that original coding does look quite interesting , so when I have time, and bearing in mind what, SpeakEasy said… <…
the two transpositions that may cause the performance hit…> I think I could have a go later at modifying your original coding slightly to remove one or more of those
Transposes.
Perhaps if you are able you could tell us where you got the original coding?, -there could be some interesting things for us to gain from there.
Alan
You do not have the required permissions to view the files attached to this post.
