Deleting empty rows does not work

[DeJans1]

Hello,

In my Excel sheet I've ran a macro which was not 100% correct yet, causing it to creating blank rows until the bottom of the sheet. It happened because the macro contains the command CTRL+SHIFT+DOWN but at the time there was no value at all in a cell, so it selected all rows until row 1,048,576. Suddenly my file is now huge and keeps freezing all the time.

I've tried selecting the 1st blank row (in this case row 100 or so), then CTRL+SHIFT+DOWN to select all rows until the bottom of the sheet, and then delete these rows. Excel tries to process this and it takes a minute or 2 to do so, but after it's done loading the rows are all still there. Somehow it won't let me delete these empty rows.

[TMS]

After you have selected all the rows, are you using the Delete key or the Delete icon on th Home Ribbon? You need to use the second one. Then it's probably best to Save, Close and Re-open the workbook,

[DeJans1]

Such a simple solution for such a pain in the ***, thank you very much!

Edit: celebrated too early, still doesn't work...

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[DeJans1]

Maybe I celebrated too early... In the test file which I have attached, it worked. But in the real file that I'm using (much bigger than only the 1 sheet in the test file; I deleted all the redundant sheets before uploading) it won't work, even with the Delete button in the Home ribbon.

[TMS]

Are you sure you are selecting the entire rows? Or choosing to delete entire rows when using the Delete icon?

[DeJans1]

Yup, pretty sure. I'm literally selecting row nr. 100, then CTRL+SHIFT+DOWN to select all rows.
Then going to Home > Cells > Delete (arrow down) > Delete sheet rows.

Loads for a minute but then nothing happens.

[Greg M]

Hi there,

Just a random thought as we don't have sight of the actual workbook - could the problem be associated with the array formulas in column P? Would it help to clear all of the unused array formulas in that column before deleting the unwanted rows?

Regards,

Greg M

[DeJans1]

Wanted to try what you proposed, so I saved the file under a different name to have a second back-up file, and suddenly all the empty rows are deleted.

I think I'm fine for now, still don't know what fixed it but okay

[TMS]

In my original answer, I said that, after deleting the rows, you needed to save, close and re-open the file. I think you may also have columns that need tidying up.

Note: you have a circular reference in cell P2: =IF(P2="Walstijd",SUM(N3:INDEX(P3:$P$82,MATCH(TRUE,(R3:$R$82=""),0))),"")

[TMS]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Greg M]

Hi again,

Many thanks for your feedback and also for the (probably undeserved!) Reputation increase - much appreciated

Glad to hear that things seem to be working correctly for you now.

Best regards,

Greg M

[DeJans1]

In my original answer, I said that, after deleting the rows, you needed to save, close and re-open the file. I think you may also have columns that need tidying up.

Note: you have a circular reference in cell P2: =IF(P2="Walstijd",SUM(N3:INDEX(P3:$P$82,MATCH(TRUE,(R3:$R$82=""),0))),"")

You were right, I wrongly assumed that couldn't be a part of the solution

Thanks for pointing me the circular reference as well!

[TMS]

You're welcome. Thanks for the rep.