VBA: Fastest way to determine last non blank row in column

[jamescooper]

This is pretty fast, is there any quicker?

Thanks.

LR = [match(2,1/(l21:l<>""))]

 

[Joe4]

I never checked for speed, but I have always used this:

LR = Cells(Rows.Count, "L").End(xlUp).Row

You can run your own speed experiments if you like.
I would expect my code is probably at least as fast as your code, though I expect any differences may be pretty much negligible and might not make much difference to the overall performance of your code.

 

[alansidman]

LR=Range("L" & rows.count).end(xlup).row

 

[jamescooper]

Thanks, doesn't appear too much difference in the speed between the 3.

 

[Fluff]

is there any quicker?

I'd say anything would be quicker, as I get an RTE 13 type mismatch error with your code.

 

[Joe4]

Thanks, doesn't appear too much difference in the speed between the 3.

Note that my code and Alan's are doing the same thing. The only difference is I am using Cells(row,column) to refer to the range and he is using Range(...)​, but that really shouldn't make any difference.

 

[alansidman]

....and Joe types faster than me.

 

[Joe4]

....and Joe types faster than me.

That's a first!
(My typing skills are severely lacking!)