whats the difference between those 2 ?
what about the speed?
any other suggestion of a faster code?
whats the difference between those 2 ?
what about the speed?
any other suggestion of a faster code?
Re: WorksheetFunction.CountA vs end(xlUp) (finding last row)
I don't know about speed, but CountA has the issue that if you have blank cells, it will provide the wrong answer.
Re: WorksheetFunction.CountA vs end(xlUp) (finding last row)
I always findEnd(xlUp) the most reliable method.
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:
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:
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
Re: WorksheetFunction.CountA vs end(xlUp) (finding last row)
thank you guys hehe
it makes a huge difference for me hehe
i need to find the lastrow for over 9000 (no pun intended) different columns lol
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.
Re: WorksheetFunction.CountA vs end(xlUp) (finding last row)
oh nvm then lol... i didnt look at ur code closely, i thought that was like per column or something.....
besides, the fact that countA skips blank cells kinda killed it...
Don’t have an account yet? Register yourself now and be a part of our community!