Use Find method against Cells collection to detemine the last used Row, Column in Excel

Function lastUsedRow() As Long
On Error Resume Next
Dim lLastRow As Integer
lLastRow = Cells.Find(What:=”*”, after:=Range(“A1″), SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
If lLastRow = 0 Then
lLastRow = 1
End If
lastUsedRow = lLastRow

End Function

Function lastUsedCol() As Long
On Error Resume Next
Dim lLastCol As Integer
lLastCol = Cells.Find(What:=”*”, after:=Range(“A1”), SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
If lLastCol = 0 Then
lLastCol = 1
End If
lastUsedCol = lLastCol

End Function

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s