Deleting Blank Rows at Bottom of Worksheet

[Hobolord]

I am running Excel 365 on Windows 10. I recently created a workbook that has a summary page for about 100 sheets. Each sheet has a link to this summary page.

I hid all rows at the bottom of the summary page for aesthetic purposes, and noticed that it increased the size of my file by about 2mb. I went in and un-hid the rows and tried to delete them all, but as soon as I delete them, they just come right back, even after saving, closing, and re-opening the file. I've tried "Clearing All" as well. I've never seen this before. I don't want to give up and make a new summary page because all of my other 100 sheets are linked to this one.

Does anyone know how to get rid of the empty rows at the bottom of the page that are causing my file to be so large?

Thank you in advance.

 

[CalcSux78]

What works for me is pressing [CTRL + End] to find the last cell within the used range. I highlight that row then scroll up to the first blank row. Hold [Shift] and select the first blank row. From there, you can right click the range and delete. Immediately, save and close the workbook and see if the file size has reduced. Repeat for any additional sheets that may have extra bloat space within the used range.

 

[My Aswer Is This]

I really do not believe deleting rows will make your file smaller.
Actually when you delete a row your really not deleting a row as to think the last row will not be 1048576

When I delete rows my last row is still 1048576

Now if you try hiding 1 million rows that may cause your file to increase in size.
But then I'm no expert on this subject.

I would think nearly no one uses all 1.4 million rows and I have never tried deleting or hiding all the rows I'm m not using

 

[Hobolord]

When I use CTRL+ End to find the last cell, it goes to Z1048576. From there, I do as you say, and highlight all rows up to the last row of data. Then I right click, select delete, immediately save the workbook, close it, and reopen. The blank rows are still there. I am stumped. This has never happened to me before.

 

[Joe4]

Maybe try copying your actual data to a new workbook, and save that.

 

[Fluff]

If you do Ctrl End, what cell do you end up in?

 

[Hobolord]

I don't want to copy to a new workbook, because I have over 100 sheets and all of them link to this summary tab.

If I do CTRL + End it goes to cell Z1048576

 

[RoryA]

Try deleting the blank rows again, then open the VB Editor (Alt+f11), go to the Immediate window (Ctrl+G) and type:

Activesheet.usedrange

then press enter, close the VBE and save the workbook. Does that help?

 

[Fluff]

Do you have any macros that work on that file?
Especially anything that inserts formulae?

 

[My Aswer Is This]

I just deleted 100 rows and when I look my lastrow is still 1048576

That's why when you delete rows your not reducing the number of rows in your sheet.
At least not for me.