Over a million extra rows... I've tried everything

[ilikered100]

I have a complex workbook template which has all of a sudden added over a milliion extra rows on each worksheet. I've tried everything I know of to delete them and nothing seems to work. For reasons I won't go into, it's not going to work for me to copy and paste the information into a new worksheet or it will take a massive amount of time as the sheets are inter-related with a lot of complex depency from one sheet to another.

No matter what I do, I still have 1048327 number of rows on most of the sheets. Any help is greatly appreciated

Here's what I've tried so far which has not worked:
1. Control-Shift -down Selecting empty rows, then Right-click-delete. The same number of rows still appear.
2. Selecting Rows and then using the Delete - Entire Row option in the menu bar.
3. Go To Special and then Selecting Blanks and Right Click Delete
4. This VBA Code:

Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long
For Each sht In ActiveWorkbook.Worksheets
lng = sht.UsedRange.Rows.Count
Next
End Sub

Thanks,
Carolyn

 

[RoryA]

In the new file formats there are always 1048576 rows.

 

[steve the fish]

So you are not getting error when deleting the cells using CNTL-SHIFT-DOWN? Have you then tried to save the book straight after doing it? Maybe then even then closing and reopening the book. That definitely works sometimes.

 

[ilikered100]

I did get an error message when I tried deleting the rows with Control-Shift-Down... It said not enough availabel resources, so i went to down to row 1,000,000 and tried deleting the rows from that number down, but even after deleting them they showed.

I also saved the file and closed it and re-opened it... the rows are still there.

 

[steve the fish]

Like rory said the rows will still be there. Im presuming you mean excel thinks the last used row is row 1048576?

 

[ilikered100]

>>Im presuming you mean excel thinks the last used row is row 1048576?
Yes... some of the worksheets have 1048576 rows and there a few that only have a few hundred rows. The spreadsheet is so large with the recognized extra rows that it takes a few minutes to open and seems unstable for lack of a better word.