Returning the last used row is pretty simple, Here are a Function and Sub
procedure examples

Public Sub Geoff_K()
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Public Function GetLastRow() As Long
Dim lRow As Long

lRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

GetLastRow = lRow
End Function


"Geoff K" wrote:

Hi Jeff

Please read the text of my question.

My aim is to find the last used row of the source wbook before I begin to
extract data.

Geoff

"Jeff" wrote:

Ron de Bruin covers how to do that.

http://www.rondebruin.nl/copy7.htm

"Geoff K" wrote:

Hi
Getting data from a closed wbook.
These wsheet formulae work fine on numerical fields but not on text.

Gets from a closed wbook the value in the last used cell of a column.
=LOOKUP(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

Gets from a closed wbook the last used row number of a column.
=MATCH(99^99,'C:\Path\[File.xls]Sheet1'!A:A)

But if either meet with a text field or even if 99^99 is substituted with
"ZZZ" then Excel goes into an infinite loop.

How can the formulae be made universal to look for either numeric or text
fields? Or if that is not possible then how might it made to work in a text
field?

T.I.A

Geoff