"My spreadsheet must be haunted" is a great opening line to an Excel question sent in just before Halloween. You have 1510 rows in Excel. You try to Delete Sheet Rows from A1511 to A1048576, but the rows keep coming back.
How can you delete all of the whitespace at the bottom of Excel?
How can you delete unused rows at the bottom of Excel?
How can you delete unused columns to the right of your Excel data?
The solution is to hide the extra rows and columns.
How can you delete all of the whitespace at the bottom of Excel?
How can you delete unused rows at the bottom of Excel?
How can you delete unused columns to the right of your Excel data?
The solution is to hide the extra rows and columns.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2436. My worksheet must be haunted!
How do you delete the blank rows from the bottom of Excel?
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen and this is a great question today a great Halloween-month question.
How do you get rid of the white space below my data and to the right of my data? I select rows 1512 through 1048576.
I tried Delete Sheet Rows and Save but it won't remove the Rows.
It still thinks I have a million rows. Maybe my worksheet is haunted.
You're right.
If I would come down here and I would delete all these rows, Alt+E D R. They're still there.
Well, what do we do about that? Well, OK, so you have 1510 rows of data today.
Data out through columns I and you don't want to see anything else.
You don't have a million rows below that data. This is all of your data.
Here's what you could do. I'm going to start in A1511.
And then Control+Shift+Down Arrow just like you did, probably.
Home, Format, Hide and unhide, Hide Rows. And then, from column J.
Control+shift+right arrow and Format, Hide and unhide, Hide columns. Success.
Your data goes from A through I and there's no white space to the right.
Your data goes down to row 1510. No whitespace below that.
It's perfect.
If you need more rows, just select something in the middle here and Alt+I+R or Home, Insert, Insert Sheet Rows.
Or if you need several rows, Insert, Insert Sheet Rows. Then you get more rows.
Seems perfect. Problem solved.
What's the harm? It didn't activate more rows.
This grew by 5K, but that's just because I put this little screenshot in here.
I mean heck, if you really only want 1511 rows in Excel, what's the harm in hiding this other rows?
Something I never thought about, but I guess it would keep me up at night knowing that I had a million rows down there.
Hide them!
Unfortunately this is a trick that's not in this book, MrExcel 2021, Unmasking Excel, but there's a lot of other good tricks in that book.
Click the “I” in the top right hand corner for more information about that.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
How do you delete the blank rows from the bottom of Excel?
Hey, welcome back to MrExcel netcast.
I'm Bill Jelen and this is a great question today a great Halloween-month question.
How do you get rid of the white space below my data and to the right of my data? I select rows 1512 through 1048576.
I tried Delete Sheet Rows and Save but it won't remove the Rows.
It still thinks I have a million rows. Maybe my worksheet is haunted.
You're right.
If I would come down here and I would delete all these rows, Alt+E D R. They're still there.
Well, what do we do about that? Well, OK, so you have 1510 rows of data today.
Data out through columns I and you don't want to see anything else.
You don't have a million rows below that data. This is all of your data.
Here's what you could do. I'm going to start in A1511.
And then Control+Shift+Down Arrow just like you did, probably.
Home, Format, Hide and unhide, Hide Rows. And then, from column J.
Control+shift+right arrow and Format, Hide and unhide, Hide columns. Success.
Your data goes from A through I and there's no white space to the right.
Your data goes down to row 1510. No whitespace below that.
It's perfect.
If you need more rows, just select something in the middle here and Alt+I+R or Home, Insert, Insert Sheet Rows.
Or if you need several rows, Insert, Insert Sheet Rows. Then you get more rows.
Seems perfect. Problem solved.
What's the harm? It didn't activate more rows.
This grew by 5K, but that's just because I put this little screenshot in here.
I mean heck, if you really only want 1511 rows in Excel, what's the harm in hiding this other rows?
Something I never thought about, but I guess it would keep me up at night knowing that I had a million rows down there.
Hide them!
Unfortunately this is a trick that's not in this book, MrExcel 2021, Unmasking Excel, but there's a lot of other good tricks in that book.
Click the “I” in the top right hand corner for more information about that.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
