Error 6: Overflow 32767 Rows
 

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

Denotes the line flagged in the debug window

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(ColOrR ow & _
":" & 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(ColOrR ow & _
":" & ColOrRow)
End If
GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, _
searchOrder:=xlByRows, LookIn:=xlValues).Row
If 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

Error 6: Overflow 32767 Rows
 

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



--
Hope that helps.

Vergel Adriano


"Juan Schwartz" wrote:

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

Denotes the line flagged in the debug window

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(ColOrR ow & _
":" & 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(ColOrR ow & _
":" & ColOrRow)
End If
GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, _
searchOrder:=xlByRows, LookIn:=xlValues).Row
If 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

Error 6: Overflow 32767 Rows
 

Can't believe I overlooked that!

Thank you very much Vergel. The error is gone.

-Juan

On Jul 23, 11:50 am, Vergel Adriano
wrote:
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

--
Hope that helps.

Vergel Adriano