How could I change this part (For r = 3 To 500) of a code so that it runs to
the last row in the worksheet instead of just row 500 as it currently does.

Hi MCheru

Copy the function and sub in a standard module of your workbook

Sub test()
Dim Lr As Long
Dim r As Long

Lr = LastRow(ActiveSheet)
For r = 3 To Lr
'code
Next r
End Sub

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"MCheru" wrote in message ...
How could I change this part (For r = 3 To 500) of a code so that it runs to
the last row in the worksheet instead of just row 500 as it currently does.

This works very good. Thank you!

"Ron de Bruin" wrote:

Hi MCheru

Copy the function and sub in a standard module of your workbook

Sub test()
Dim Lr As Long
Dim r As Long

Lr = LastRow(ActiveSheet)
For r = 3 To Lr
'code
Next r
End Sub

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"MCheru" wrote in message ...
How could I change this part (For r = 3 To 500) of a code so that it runs to
the last row in the worksheet instead of just row 500 as it currently does.

Hi Ron,


I've always used another little UDF function, I've created on my own, to
find the last row.
But I've also always never liked my own function, so when I saw yours i was
delighted, since it looked much better than mine.

But...
Your function doen't seem to find the right last row on a protected sheet?

Reason seems to be that the .Find method doesn't search protected cells.
Is it possible to make your function work on protected sheets as well, since
I really like your way of finding last row much better than mine?


TIA,


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

Hi Charlotte

I not see you problem
If you want send me a test workbook private so I can look at it



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Charlotte E" <@ wrote in message ...
Hi Ron,


I've always used another little UDF function, I've created on my own, to
find the last row.
But I've also always never liked my own function, so when I saw yours i was
delighted, since it looked much better than mine.

But...
Your function doen't seem to find the right last row on a protected sheet?

Reason seems to be that the .Find method doesn't search protected cells.
Is it possible to make your function work on protected sheets as well, since
I really like your way of finding last row much better than mine?


TIA,


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

No problem, Ron,


The spreadsheet is in Danish so you wouldn't understand it anyway :-)

Problem with your function is just that is doesn't find last row, if last
row is hidden and protected on a protected worksheet.
Take a look: www.EXCELGAARD.dk\LastRow.XLSm

But, I found a way around it :-)


Thanks anyway.




Ron de Bruin wrote:
Hi Charlotte

I not see you problem
If you want send me a test workbook private so I can look at it




"Charlotte E" <@ wrote in message
...
Hi Ron,


I've always used another little UDF function, I've created on my
own, to find the last row.
But I've also always never liked my own function, so when I saw
yours i was delighted, since it looked much better than mine.

But...
Your function doen't seem to find the right last row on a protected
sheet? Reason seems to be that the .Find method doesn't search protected
cells. Is it possible to make your function work on protected sheets as
well, since I really like your way of finding last row much better
than mine? TIA,


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