Friday, July 9, 2010

Find Last Cell, Column And Row in Excel Worksheet

Hi All,

Please find below a small code snippet, which you can use to find the last Used Cell, Row or Column.

Public Function LastUsedCell() As Range
Dim rng As Range
Dim activeSht As Worksheet
On Error GoTo ErrHandler

Set activeSht = activeSheet

Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)

'return value
Set LastUsedCell = rng
Exit Function
ErrHandler:
Set LastUsedCell = Nothing
End Function

Public Function LastUsedRow() As Long
Dim rng As Range
Dim activeSht As Worksheet
On Error GoTo ErrHandler

Set activeSht = activeSheet

Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)

'return value
LastUsedRow = rng.Row
Exit Function
ErrHandler:
LastUsedRow = 0

End Function


Public Function LastUsedColumn() As Integer
Dim rng As Range
Dim activeSht As Worksheet
On Error GoTo ErrHandler

Set activeSht = activeSheet

Set rng = activeSht.Range("A1").SpecialCells(xlCellTypeLastCell)

'return value
LastUsedColumn = rng.Column
Exit Function
ErrHandler:
LastUsedColumn = 0

End Function


Hope this helps :)

Thanks,
Vikas

No comments: