Where's the Bug

[Factor3]

Working on a VERY simple function here, I would like to find the bug instead of a new way to do it (I would assume I'm pretty close b/c it's so easy), but I know people are busy, so I'll take whatever you brilliant people are willing to give:friends:

I'm creating a function to find the last row (to use in another sub). This Code WORKS:

Sub Last_Row()

lastRow = Cells(Rows.Count, 1).End(xlUp).Address

End Sub
So I would assum my function would work too, but it doesn't:

Function LROW(rng) As Range

LROW = Range(rng).Cells(Rows.Count, 1).End(xlUp).Address

End Function

I get the generic error "Object variable or With block variable not set" when I run it through my tester Sub:

Sub TestLROW()

Dim Test As Range

Test = LROW("A1")

End Sub
I've tried the function another way too that more closely mirrors my original working Sub:
Function LROW(col) As Range

LROW = Cells(Rows.Count, col).End(xlUp).Address

End Function
This one gets the same error message..... UGH!

Thank you in advance, you people are great!

[Kenneth Hobs]

I recommend always using Option Explicit.

Your errors are that your function expects a range object to be returned. Use Set to set a Range object.

Also, be aware that you are getting the last row for column A, not for the usedrange in the sheet. You can use what I did in the Sub t, to get the first and last cells information like Address, Row, Column, etc.

Here are some that I use. The last one returns the sent range less the first row.
Sub t()
Dim r As Range
Set r = Range("Y4", Cells(Rows.Count, "Y").End(xlUp).Address)
MsgBox r.Address
MsgBox Cells(r.Row, r.Column).Address
MsgBox r.Cells(r.Rows.Count, r.Columns.Count).Address

End Sub

'=LastNBRow(A3:G10)
Function LastNBRow(rng As Range) As Long
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = rng.Find(What:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
LastNBRow = LastRow
End Function

'=LastNBCol(A3:G10)
Function LastNBCol(rng As Range) As Long
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = rng.Find(What:="*", After:=rng.Cells(rng.Rows.Count, rng.Columns.Count), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
LastNBCol = LastColumn
End Function

Function RangeLR1(aRange As Range) As Range
Set RangeLR1 = Range(Cells(aRange.Row + 1, aRange.Column), Cells(aRange.Rows.Count, aRange.Columns.Count))
End Function

[mdmackillop]

These will accept letter or numerical values, with an option to get the next row/column by adding an offset

Option Explicit

'Last Row
Function LR(Col As Variant, Optional OS As Long)
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
LR = Cells(Cells.Rows.Count, Col).End(xlUp).Offset(OS)
End Function

'Last Column
Function LC(Rw As Variant, Optional OS As Long)
Application.Volatile
If Not IsNumeric(Rw) Then Rw = Rows(Rw).Row()
LC = Cells(Rw, Cells.Columns.Count).End(xlToLeft).Offset(, OS)
End Function

[Factor3]

Thanks for that incredible input. I'm trying to debug and DO the code so I can half understand it.... Dr. Mack, When i run your functions I get the value for the last row, or last column. How would I alter the code so that it returns the Address of the last row/column? When I change the last part of the function to .Address, it gets angry at me and doesn't work:dunno

[mdmackillop]

Sub test()
MsgBox LR(5).Address
End Sub



'Last Row
Function LR(Col As Variant, Optional OS As Long) As Range
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
Set LR = Cells(Cells.Rows.Count, Col).End(xlUp).Offset(OS)
End Function

[Factor3]

Thank you so much, I really appreciate it.

[Aussiebear]

... it gets angry at me and doesn't work:dunno

As soon as you hear the bell ring, race in and grab Excel by the throat, and shake it around a bit....

It works for me.