# Rows in worksheet has run amok

I don't know what happened to cause this but one of my worksheets shows end of file with 1,041,584 rows. My actual data only uses 2,348 rows. When I use keystrokes ctrl-end it shows me row 1,041,584 after a long delay. Saving the file also seems to take much longer. I've tried selecting the rows between 2348 and 1,041,584 and after I get a warning msg saying the operation will take a long time, I press ok and it does complete but when I do ctrl-end it still shows the 1+ Million rows.

I have another workbook with same number of worksheets and same functions in all the worksheets and they use about 4,000 kb of space - the one with the runaway rows uses almost 12,000 kb.

I am using excel 2016.

Thanks for any insight/solution.

Once you have deleted those empty rows between 2348 and 1041584, you must save the workbook in order for the change to take effect.

Hope this helps.

Pete

Thanks and that works however I have a problem trying to delete the rows. When I select the rows using the row number and use the down key to select the rows it takes a very long time - I used that method for about 70,000 rows, then deleted them then saved the file and the size used dropped.

When I try Selecting the row and hit ctrl-shift-end keys it hilites them (very slowly) but when I hit delete, then save, then close - the file size does not change - I believe that is just deleting the cell content.

Is there a quicker way to delete the rows - I think it will take me at least one hour of scrolling the row numbers column to delete all the rows.

By the way the file size is about 240,000 kb at this point.

Thanks for your help

QUICKER CLEANER WAY ... is to make a new sheet and just copy the rows with data, delete old sheet, rename new sheet to old sheets name.

I reference cells in my problem worksheet from another worksheet so I think I'd get an invalid ref message on that worksheet. Not sure if it would resolve after re-naming.

I did find a quicker way though selecting the extra rows using the vba rows command then deleting them:

Please Login or Register  to view this content.

Thanks again for your help and I will mark this thread closed

NO PROBLEM ... If you cut and paste instead of copy/paste the links will follow so you will still have the original formula working.

This will even be quicker

Sub DELETE_BLANK_ROWS()
'
' DELETE_BLANK_ROWS Macro
'

'
Rows("2349:900000").Delete Shift:=xlUp
End Sub

Here's a macro that delete rows that are blank in column A of the active sheet

Please Login or Register  to view this content.

Ok, got it, that's good to know for any future issues like this.

Thanks Jeff,

I will keep that documentation in case this re-occurs.

Best Regards,
Pongmeister