Application.WorksheetFunction.Index(array, row, column) If you specify a zero value for row or column, then you’ll get the entire column or row that is specified. Example: Application.WorksheetFunction.Index(array, 0, 3) This will give you the entire 3rd column. If you specify both row and column as non-zero, then you’ll get only the specific element. There is no … Read more
Using following simple formula is much faster =LOOKUP(2,1/(A:A<>””),A:A) For Excel 2003: =LOOKUP(2,1/(A1:A65535<>””),A1:A65535) It gives you following advantages: it’s not array formula it’s not volatile formula Explanation: (A:A<>””) returns array {TRUE,TRUE,..,FALSE,..} 1/(A:A<>””) modifies this array to {1,1,..,#DIV/0!,..}. Since LOOKUP expects sorted array in ascending order, and taking into account that if the LOOKUP function can not … Read more
Evaluate might suit: http://www.mrexcel.com/forum/showthread.php?t=62067 Function Eval(Ref As String) Application.Volatile Eval = Evaluate(Ref) End Function