Defining a Range

[Guest]

I want to define the last used row on a worksheet. I could use
"SpecialCells(xlLastCell)" however this also includes extra rows that were
used previously but are now empty.

I think there is a function that gives the last cell or row in a block/table
of cells but I can't find it.

Any ideas?

Kaval

 

[Don Guillett]

I used Ron DeBruin's google search addin
http://tinyurl.com/4d2yg

 

[Gord Dibben]

Kaval

Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", after:=[A1], _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row
MsgBox LastRow
End If
End Sub

Gord Dibben Excel MVP