Faastest way to find last previous entry?

[Ed from AZ]

(In Excel 2003) When my user selects an item from a drop-down on a
UserForm, I need to search through the range of cells on the worksheet
to find the last previous entry of that item. The item will probably
be entered multiple times, so I need the one closest to the bottom of
the list.

Would it be faster to use a MATCH or LOOKUP function? Or do a Find?
Iteration is probably the slowest, especially when this thing hits a
few thousand entries, yes?

Ed

 

[paul.robinson]

Hi
Find is probably the fastest. To find the last entry matching "grp" in
column A

Set lastCell = Range("A:A").Find("grp",
SearchDirection:=xlPrevious)

This will search backwards from A1 and hence find the last cell with
"grp" in first.
regards
Paul

 

[Ed from AZ]

Thanks, Paul. I appreciate the boost.

Ed

Hi
Find is probably the fastest. To find the last entry matching "grp" in
column A

   Set lastCell = Range("A:A").Find("grp",
SearchDirection:=xlPrevious)

This will search backwards from A1 and hence find the last cell with
"grp" in first.
regards
Paul





- Show quoted text -