Posts by Kyle123

Kyle123 · Sep 7th 2012

Re: WorksheetFunction.CountA vs end(xlUp) (finding last row)

I don't see how it does, I found the last row for 10,000 items above and the difference between the two is indistinguishable in real terms.

Kyle123 · Sep 7th 2012

Re: Is it possible for a SELECT to list a Field numerically?

Maybe, Something like:

SELECT *
FROM Table1
ORDER BY Field1 ASC

Though I still don't really have a clue what you're looking for

Kyle123 · Sep 7th 2012

Re: Is it possible for a SELECT to list a Field numerically?

Can you qualify your question a little more, I'm not sure what you mean - the above will work of you have a table named table1

Kyle123 · Sep 7th 2012

Re: WorksheetFunction.CountA vs end(xlUp) (finding last row)

End is fractionally faster in my tests, but probably not fast enough to make much of a difference:

Code

Sub test()Dim lr As Long
Dim x As Long
Dim tim As cTimer




Const it As Long = 10000




Set tim = New cTimer




tim.StartCounter
For x = 1 To it
    lr = Application.CountA(Sheet1.Columns(1))
Next x
Debug.Print "count - " & tim.TimeElapsed




tim.StartCounter
For x = 1 To it
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Next x
Debug.Print "End - " & tim.TimeElapsed
End Sub

Display More

Results:

Code

count -     126.815535765441
End -       98.7825480104628
------------------------------
count -     137.856670507087
End -       98.6848270070511
------------------------------
count -     120.370857901167
End -       99.0859739571295
------------------------------
count -     134.747000768382
End -       98.9695119393648
------------------------------
count -     132.033123404225
End -       100.032395181953
------------------------------
count -     126.313098094931
End -       100.494673535992
------------------------------
count -     126.738340634892
End -       101.338465396502
------------------------------
count -     132.271848229911
End -       99.2077905504236
------------------------------
count -     122.927221501834
End -       98.453241615404
------------------------------
count -     122.466728006304
End -       98.2247797261492
------------------------------

Display More