Have you tried doing simply theSheet.usedrange as I suggested previously
(after clearing formats in rows & col's below & right of last data cell).

Regards,
Peter T

"Bob" wrote in message
...
Thanks Peter. Is there anyway of resetting the scrollbars without closing
the file? You see, Once I know the last row (or column), I use the rows
under it for my own use. I basically, cut rows from above the last row,
and paste them below it to reorder the rows (kind of like sorting, but I
do not sort). Then once I have the rows in the new order, I cut them from
below the last row and put them in the above the last row. So, if I do
not change the scrollbar range, the user may get confused as what
happened. So, I need to delete the new rows below the last row, and reset
the scrollbar without closing the file. Thanks for your help.

Bob

"Peter T" <peter_t@discussions wrote in message
...
Another one

Sub test()
Dim nr As Long, nc As Long, cel As Range, s As String

If LastDataCell(ActiveSheet, nr, nc, cel) Then

s = "Last data cell " & cel.Address & vbCr & _
"row " & nr & vbCr & "col " & nc
Else
s = "empty sheet"
End If
MsgBox s

End Sub

Function LastDataCell(ws As Worksheet, _
lastRow As Long, lastCol As Long, _
Optional rCell As Range) As Boolean
Dim X As Variant

On Error Resume Next
LastDataCell = True
With ws
lastRow = .Cells.Find("*", .Range("A1"), xlFormulas, _
xlPart, xlByRows, xlPrevious, 0).Row
lastCol = .Cells.Find("*", .Range("A1"), xlFormulas, _
xlPart, xlByColumns, xlPrevious, 0).Column
X = .Cells.Find("") 'reset Find

If Err.Number < 0 Then ' an empty sheet
lastRow = 1
lastCol = 1
LastDataCell = False
End If
Set rCell = .Cells(lastRow, lastCol)
End With

End Function


To reset the scrollbars, clear any formats below/right of the last data
cell, ie delete rows & columns. Then you'll probably need to call
ws.usedrange to fully reset (it'll get done when you save, close,
re-open)

Regards,
Peter T

"Bob" wrote in message
...
Hi All:

I have two questions that are sort of related.

1-I was wondering is anyone knows in excel, how we could know, the last
row
and column on the spreadhseet that has any data in it?

2- Let's say that you have data on the Range A1:A20, and A1000. At
this
point the vertical scroll bar is very small. Then if you delete A1000,
is
there a way to reset the limits on the vertical scroll bar? In the
regular
VB, you have Scrolbar.min and scrolbar.max. Is there such a thing in
VBA for
Excel?

Thanks for your help.

Bob