Need help... I got this macro from ExcelTip.com (thanks to Ron de Bruin)
basically to find the last filled row.

I would like to use this as a worksheet function in cell (A2). As
such, I entered =LastRow(), but the result is #VALUE. I did not pass
any parameter into the function as I assume it will take the current
Activesheet.

Can someone help to show some lights with regards to this problem?
Thanks in advance.


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
slc
------------------------------------------------------------------------
slc's Profile: http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=400016

Find doesn't work in a UDF pre Excel 2002.

If you have Excel 2000 or earlier, try this

Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function

and use like so

=LastRow(A:E)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"slc" wrote in message
...

Need help... I got this macro from ExcelTip.com (thanks to Ron de Bruin)
basically to find the last filled row.

I would like to use this as a worksheet function in cell (A2). As
such, I entered =LastRow(), but the result is #VALUE. I did not pass
any parameter into the function as I assume it will take the current
Activesheet.

Can someone help to show some lights with regards to this problem?
Thanks in advance.


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
slc
------------------------------------------------------------------------
slc's Profile:
http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=400016

Hi Bob

Working for me in Excel 2000


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Find doesn't work in a UDF pre Excel 2002.

If you have Excel 2000 or earlier, try this

Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function

and use like so

=LastRow(A:E)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"slc" wrote in message
...

Need help... I got this macro from ExcelTip.com (thanks to Ron de Bruin)
basically to find the last filled row.

I would like to use this as a worksheet function in cell (A2). As
such, I entered =LastRow(), but the result is #VALUE. I did not pass
any parameter into the function as I assume it will take the current
Activesheet.

Can someone help to show some lights with regards to this problem?
Thanks in advance.


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
slc
------------------------------------------------------------------------
slc's Profile:
http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=400016

Hi

This is not a worksheet function
You must use it in VBA like this LastRow(Activsheet)

For a worksheetfunction see this from Harlan Grove

Use it like this
=lc()

Function lr(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Rows.Count

For i = n To 1 Step -1
Set c = ur.Cells(i, 1)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlToRight).Value) Then Exit For
Next i

lr = ur.Row + i - 1
End Function


Function lc(Optional r As Range) As Variant
Dim ur As Range, c As Range, i As Long, n As Long

'there arguments for & against volatility - optional to uncomment
Application.Volatile

If r Is Nothing Then Set r = Application.Caller
If Not TypeOf r Is Range Then Set r = ActiveCell

Set ur = r.Parent.UsedRange
n = ur.Columns.Count

For i = n To 1 Step -1
Set c = ur.Cells(1, i)
If Not IsEmpty(c.Value) Then Exit For
If Not IsEmpty(c.End(xlDown).Value) Then Exit For
Next i

lc = ur.Column + i - 1
End Function



--
Regards Ron de Bruin
http://www.rondebruin.nl


"slc" wrote in message ...

Need help... I got this macro from ExcelTip.com (thanks to Ron de Bruin)
basically to find the last filled row.

I would like to use this as a worksheet function in cell (A2). As
such, I entered =LastRow(), but the result is #VALUE. I did not pass
any parameter into the function as I assume it will take the current
Activesheet.

Can someone help to show some lights with regards to this problem?
Thanks in advance.


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
slc
------------------------------------------------------------------------
slc's Profile: http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=400016

Oops

See reply to the OP


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi Bob

Working for me in Excel 2000


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Find doesn't work in a UDF pre Excel 2002.

If you have Excel 2000 or earlier, try this

Function LastRow(rng As Range)
Dim temp, temp1
Dim col As Range
With Application.Caller.Parent
For Each col In rng.Columns
temp = Cells(Rows.Count, rng.Column).End(xlUp).Row
If temp temp1 Then temp1 = temp
Next col
End With
LastRow = temp1
End Function

and use like so

=LastRow(A:E)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"slc" wrote in message
...

Need help... I got this macro from ExcelTip.com (thanks to Ron de Bruin)
basically to find the last filled row.

I would like to use this as a worksheet function in cell (A2). As
such, I entered =LastRow(), but the result is #VALUE. I did not pass
any parameter into the function as I assume it will take the current
Activesheet.

Can someone help to show some lights with regards to this problem?
Thanks in advance.


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


--
slc
------------------------------------------------------------------------
slc's Profile:
http://www.excelforum.com/member.php...fo&userid=1439
View this thread: http://www.excelforum.com/showthread...hreadid=400016