FUNCTION

S

SURESH

Hi dear friends,

Anyone can help me in this prob.

i want to know in my spreadsheet which is the last entry (i mean last cell
which is having data)

thanks in advance

suresh tp
 
D

Dave Peterson

If you want the last used cell in a particular column, you can use:

dim LastRow as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

If you can trust the usedrange (excel may not think it's the same as you
think--it remembers if you've used a cell and then cleared it).

Dim LastRow As Long
With Worksheets("sheet1").UsedRange
LastRow = .Cells(.Cells.Count).Row
End With

Debra Dalgleish has some techniques at:
http://www.contextures.com/xlfaqApp.html#Unused
to reset that lastused cell.

=======
Included in Debra's site is code that can find the last cell with something in
it:

Dim LastRow As Long
Dim LastCol As Long

With Worksheets("sheet1")
LastRow = 0
LastCol = 0
On Error Resume Next
LastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
LastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
End With

MsgBox LastRow & vbLf & LastCol
 
J

Jef Gorbach

That works...however its my understanding UsedRange only updates when the
file is first Opened, so instead to find the last cell used in column.A, for
example, I use: Range("A65536").end(xlup).address
or range("A65536").end(xlup).row for the final row to process in a
range/loop.
(or Cells(65536,1).endxl(up) if you prefer the Cells(row, col) version)
 
J

Jerry W. Lewis

UsedRange is continuously maintained, but you would need a Volatile UDF
to automatically update the result back to a worksheet.

Jerry
 
Top