How to find last row/column with vba?
Hi!
The following function is very useful and covers a wide spectrum of
situations (whole sheets, individual rows or cols)
Public Function GetLast(Optional BookName As String, Optional SheetName
As String, Optional Column As Boolean, Optional ColOrRow As String) As
Integer
' GetLast Class method by Mark Bigelow
' mjbigelow at hotmail dot com
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
End Function
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.
etc
Alf
|