PDA

View Full Version : Last Row



mdmackillop
02-01-2007, 02:07 PM
I'm suffering RSI with typing "Cells(Rows.Count,1).End(xlUp)" so I'm going for a function. Any other suggestions?
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

Bob Phillips
02-01-2007, 02:26 PM
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
02-01-2007, 02:29 PM
Even better then, I can just delete the IsMissing line.
Thanks Bob

Ken Puls
02-03-2007, 09:31 PM
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
02-04-2007, 04:34 AM
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. :)
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



Edit:
For more on this subject see The Last Row (http://www.vbaexpress.com/forum/showthread.php?t=9774)

Bob Phillips
02-04-2007, 04:40 AM
You should declare the arguments, and qualify all



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

mdmackillop
02-04-2007, 04:48 AM
Thanks Bob,
Improvements always welcome. I'll make Col a variant though, so I can pass either the column letter or number.

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