How do I delete empty rows appending to the bottom of my excel-spreadsheet?

Question

There are like 6000 empty rows under my excel-table and I have no clue how to delete them. Simply deleting them by right-click does ... well ... nothing.

Well, the other worksheets only go as far as their is actually content plus a few cells. It's only one sheet that has those 6000+ cells. It's a pain in the *** to scroll. That's the problem.

@quertykph: This is very nice. But i have a hard time with the last part, porbably because I have a mac and the keybindings are somewhat different. Can you describe what function (+ and ) would trigger?
Hiding the cells is practical, but I suppose the other solution you proposed is somewhat more elegant. I would appreciate if you can elaborate. :-)
@VBAXLMan: You're right there are 60.000+ rows, not 6000+. And painting those cells black is not really changing anything, except those cells are now ... well, black.

Answer 1

I think you're a keyboard shortcut person. When you're in the sheet with the empty rows, if you press + the cursor will go to last row with data. But for the one with extra 6000 rows, the cursor goes 6000 rows past last row of data.

I've had the same issue. Here's what I do.

1) Position cursor in cell A1
2) Press +
3) Press
4) Hold + and press once. Release keys
5) Hold press once. Release keys
6) Hold + and press once.

This will select those nefarious 6000 rows.

8) Press +. Release Keys
9) Press
10) Save file

Your done!

Alternately, you could just hide the rows you don't want to see.


Hope this helps.
Kind Regards,
QwertyKPH @ Yahoo!

Answer 2

This should work assuming Excel does not have a built in feature to do it. In an unused column put sequential numbers to be used to return the sequence at the end. In another column put 1 and then 2. Propagate it down so you have 1,2,1,2,1,2...... Now sort the sheet on the 1 and 2 column and every other row will be grouped together. Do the formatting. Resort using the sequential number column. Remove the two columns. ✩

Answer 3

You can not delete these cells
Each sheet (In Excel 2003 and earlier) have 65536 rows, so if you delete them, they will be simply replaced

You can do a trick, color these cells in black

Enjoy my profile, I am the VBAXLMan

Answer 4

Select autofilter
Select any useful column where you are sure there a blank data
Click on the autofilter, select non-blanks
Select top left to select all cells
copy and paste over to a new worksheet.
Work with the new spreadsheet.

Answer 5

you can't delete them and if you don't type anything in those cells then their not gonna print anyways.

Answer 6

DUH! Each worksheet will show it's total cells. Only the cells with something in them will print. That's all you should be concerned about.