Error 6: Overflow 32767 Rows

[Juan Schwartz]

I understand that the variable being used is an integer and needs to
be a Long, but I don't understand how to do it with the specific
snippet I used...

Any help would be appreciated.

-J

Public Function GetLast(Optional BookName As String, Optional
SheetName _
As String, Optional Column As Boolean, Optional ColOrRow As String) As
Integer

Dim objFind As Range

If BookName = "" Then
BookName = ActiveWorkbook.Name
End If

If SheetName = "" Then
SheetName = Workbooks(BookName).ActiveSheet.Name
End If

'On Error Resume Next
If Column = True Then
If ColOrRow = "" Then
Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange
Else
Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrRow & _
":" & ColOrRow)
End If
GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, _
searchOrder:=xlByColumns, LookIn:=xlValues).Column
If Err.Number <> 0 Then
GetLast = 1
Exit Function
End If
Else
If ColOrRow = "" Then
Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange
Else
Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrRow & _
":" & ColOrRow)
End IfIf Err.Number <> 0 Then
GetLast = 1
Exit Function
End If
End If
'On Error GoTo 0

'Call the function with r=GetLast (for last row in sheet)
'or r=GetLast( , , , "A") for last row in col A
'or c=GetLast( , , True, "15") for last column in row 15.

End Function

 

[Guest]

Juan,

Replace "Integer" with "Long"...


Public Function GetLast(Optional BookName As String, Optional
SheetName _
As String, Optional Column As Boolean, Optional ColOrRow As String) As
Long

 

[Juan Schwartz]

Can't believe I overlooked that!

Thank you very much Vergel. The error is gone.

-Juan