How do I remove 10,000+ empty rows below my table?

[Guest]

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.

 

[Brad]

ASAP has its ubiquitous (and free) mega utility and among them is a delete
all unused rows command. There are so many useful utilities on this add-in,
I can't imagine not having it available for quick requirements like the one
you have. Google it up and take a look. Brad

 

[Chip Pearson]

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)

 

[Guest]

Select the rows
Then...trom the Excel main menu:
<edit><delete><entire row>

Then save the workbook.
(In some versions of Excel you must Save/Close/Re-open the workbook

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

I did try this but couldn't really select the bottom row as it seemed to be
endless, I tried a block at a time but it seemed to be hopeless. Thanks
anyway.
Sheila

 

[Guest]

This looks like it could do the trick. Of course now I've gone back into
excel the problem has gone away, it's been with me for weeks, there you go
that's Microsoft for you... hopefully next time it happens the ASAP software
will come into play. Thanks very much for that. Sheila.

 

[Guest]

This looks a little complicated for a simple person like me so I have
downloaded the ASAP software suggested by another user and I have solved the
problem; but I have printed out your code just in case for future problems.
Thanks. Sheila

 

[David McRitchie]

Hi Shelia,
You really want to use a macro (or an addin) to do this quickly,
but as far as trying to move the cursor down endless rows or
across a lot of columns you can use the name box at the far
left of the formula bar.
15000:65536 will select the rows 15000 through 65536
J:IV will select the columns J through IV

You will run across a lot of suggestions to use a provide macro
solution in these newsgroups. The programming newsgroup
is where question specific to macros (and user defined functions)
are asked and answered. For instance Chip provided a macro, if
you want to try that macro or any other macro you could install it by starting at
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro