View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
AlfD[_5_] AlfD[_5_] is offline
external usenet poster
 
Posts: 4
Default 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