Excel often gets confused about what the actual used range of sheet is. It
often includes rows that are not in use. You can reset this by using code
like

Sub ResetUsedRange()
Dim LastCell As Range
Set LastCell = Cells.Find(what:="*", LookIn:=xlFormulas,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious,
MatchCase:=False)
Range(LastCell(2, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Delete
End Sub

This first finds the last used cell using Find with a What parameter of '*',
meaning find anything, starting at the last cell ("IV65536" in XL before
2007) move toward the top of the sheet. It then delete all the rows starting
one row below LastCell all the way down to Rows.Count = 64K in XL before
2007.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"Frustrated Excel User" <Frustrated Excel
wrote in message ...
My table has 1000 rows but the right hand scroll bar keeps on going down
beyond 10,000 rows, can anyone tell me why or how to get rid of them?
Thanks.