Last Row Code

  • Thread starter Thread starter MCheru
  • Start date Start date
M

MCheru

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
 
This works very good. Thank you!

Ron de Bruin said:
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
Click to expand...
 
Back
Top