Displaying over 1 million rows

Good evening,

This can happen when you have referenced your entire spreadsheet, sometimes by mistake.....

One solution:

Go to your last active row (around the 3000th row)

Select your entire row (the 3001st row)

Click simultaneously on Ctrl + Shift + Down Arrow (you will then have selected all the blank rows below your table)

Right-click, then "Delete"

Save, close the file, and reopen.....

Have a good evening.

Another method to try (if the sheet does not contain macros):
Select rows 1 to 3000; copy;
take a blank sheet; paste.
--
Retirement is great! Especially in the West Indies ... :-)
☻ Raymond ♂

on what?
--
see you+

Never attribute to malice that which can be adequately explained by incompetence. Software is like sex; it's better when it's free.

Hello.

There must be a non-empty cell in row 1,045,160.

Select and delete all rows from 3,001 to 1,045,160.

Best regards.

Hello forum

A little VBA macro to tidy up, by Laurent Longre

 Sub Clean() 'Laurent Longre mpfe, formatting GeeDee Dim Sht As Worksheet, DCell As Range, Calc As Long, Nothing As String, Before As Double, range As Range On Error Resume Next Calc = Application.Calculation ' ---- memorization of recalculation state '------------------------------------------------------------ MsgBox "For the active workbook: " _ & Chr(10) & ActiveWorkbook.FullName _ & Chr(10) & "in each worksheet" _ & Chr(10) & "searches for the area containing data," _ & Chr(10) & "resets the last used cell" _ & Chr(10) & "and optimizes the size of the Excel file", _ vbInformation, _ "according to LL by GeeDee@m6net.fr" '------------------------------------------------------------- MsgBox "Initial size of this workbook in bytes" _ & Chr(10) & FileLen(ActiveWorkbook.FullName), _ vbInformation, ActiveWorkbook.FullName '------------------------------------------------------------ With Application .Calculation = xlCalculationManual .StatusBar = "Cleaning in progress..." .EnableCancelKey = xlErrorHandler .ScreenUpdating = True End With '-------------------- processing For Each Sht In Worksheets Before = Sht.UsedRange.Cells.Count Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address '-------------------Processing the found area If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then Set DCell = Sht.Cells.Find(What:="*", _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious)(2) '----------------Deletion of unused rows If Not DCell Is Nothing Then Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete Set DCell = Nothing Set DCell = Sht.Cells.Find(What:="*", _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious)(2) '----------------Deletion of unused columns If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete End If Nothing = Sht.UsedRange.Address End If ActiveWorkbook.Save '---------------------Message for the processed sheet MsgBox "Sheet name: " _ & Chr(10) & Sht.Name _ & Chr(10) & Format(Sht.UsedRange.Cells.Count / Before, "0.00%") & _ " of the initial size", vbInformation, ActiveWorkbook.FullName Next Sht '--------------------End of processing message MsgBox "Optimized size of this workbook in bytes " & Chr(10) & _ FileLen(ActiveWorkbook.FullName), _ vbInformation, _ ActiveWorkbook.FullNameActive '-------------------- Application.StatusBar = False Application.Calculation = Calc End Sub 

Mytå

Editing: Code indentation
What pretension to claim that computing is recent
Adam and Eve already had an Apple! [MsProject 2003(FR), Excel 2003-2007(FR)]

Good evening.

Thank you "Mytå".

I will set this macro aside in my VBA library and test it.

It can always be useful.

Best regards.