Last Row

[mdmackillop]

I'm suffering RSI with typing "Cells(Rows.Count,1).End(xlUp)" so I'm going for a function. Any other suggestions?
[VBA]Sub Test()
Dim rng As Range
Set rng = Range(Cells(1, 3), LRw(3,1))
End Sub

Function LRw(Col, Optional Oset As Long) As Range
If IsMissing(Oset) Then Oset = 0
Set LRw = Cells(Rows.Count, Col).End(xlUp).Offset(Oset)
End Function[/VBA]

[Bob Phillips]

You can't do IsMissing with a typed argument, it has to be type Variant. By declaring it as long, it will be 0 if not present.

[mdmackillop]

Even better then, I can just delete the IsMissing line.
Thanks Bob

[Ken Puls]

Personally, I'd add a worksheet to the parameters. The function is then a litel more robust as you can pass it whatever sheet you want to test without having to activate it first.

[mdmackillop]

Personally, I'd add a worksheet to the parameters. The function is then a litel more robust as you can pass it whatever sheet you want to test without having to activate it first.

[vba]Option Explicit

Sub Test()
Dim WS As Worksheet
Set WS = Sheets(1)
Range(WS.Cells(2, 2), LRw(WS, 2, 1)).Interior.ColorIndex = 6
End Sub

Function LRw(LrSh, Col, Optional OSet As Long) As Range
Set LRw = LrSh.Cells(Rows.Count, Col).End(xlUp).Offset(OSet)
End Function

[/vba]

Edit:
For more on this subject see The Last Row

[Bob Phillips]

You should declare the arguments, and qualify all

[vba]

Function LRw(ByVal LrSh As Worksheet, ByVal Col As Long, Optional ByVal OSet As Long) As Range
With LrSh
Set LRw = .Cells(.Rows.Count, Col).End(xlUp).Offset(OSet)
End With
End Function
[/vba]

[mdmackillop]

Thanks Bob,
Improvements always welcome. I'll make Col a variant though, so I can pass either the column letter or number.

[vba]Option Explicit

Sub Test()
Dim WS As Worksheet
Set WS = Sheets(1)
Range(WS.Cells(2, 2), LRw(WS, 2, 1)).Interior.ColorIndex = 6
Range(WS.Cells(2, "D"), LRw(WS, "D", 1)).Interior.ColorIndex = 8
End Sub

Function LRw(ByVal LrSh As Worksheet, ByVal Col As Variant, Optional ByVal OSet As Long) As Range
With LrSh
Set LRw = .Cells(.Rows.Count, Col).End(xlUp).Offset(OSet)
End With
End Function

[/vba]