Sorting Causes Worksheet to End at 16,000 or So

jdanniel

Member
Joined
Jul 16, 2018
Messages
84
Reaction score
0
Points
6
Excel Version(s)
MS365
Hi everyone, I'm using Excel as part of Office 365. I have a spreadsheet for my music collection, and it does something that confuses me. It's not serious or critical, but I'd like to know why it does this, and if it can be prevented.

I've converted the spreadsheet to a table, so I can use slicers. When I use CTRL + END to go to the end of the list, Excel goes precisely to the very last row that has been entered. This is perfect and exactly what I want.

However, if I sort any column in the worksheet, then when I try scrolling down to the end via CTRL + END, it goes way, far down to the last blank row, which is something like 16,000!

I know how to fix this when it happens, by using PAGE DOWN to get to the final row, and then entering new data in the next available open row. Not a big deal to fix, but I was wondering if there's a way to prevent Excel from going all the way to the very end whenever I sort. I'm wondering if I may not have created the table correctly? Or is there a reason why this happens when I sort a column?

Thank you! Jd
 

Attachments

  • carmusic.xlsm
    54.8 KB · Views: 11
.
If you use CTRL / DOWN ARROW it will get you to the last used row, after filtering.
 
Resize your table to:
2020-10-27_004301.png

to get:
2020-10-27_004426.png

It currently goes down a lot further:
2020-10-27_004537.png

Click the pics to get a better view.
Don't worry, adding data to the bottom of the table will automatically expand the table.
 
Last edited:
Thank you both for your replies and assistance. Jd
 
You are welcome.
 
Back
Top