FUNCTION

  • Thread starter Thread starter SURESH
  • Start date Start date
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
 
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
 
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)
 
UsedRange is continuously maintained, but you would need a Volatile UDF
to automatically update the result back to a worksheet.

Jerry
 
Back
Top