Displaying over 1 million rows
Solved
Boklyne
-
Mytå Posted messages 4246 Registration date Status Contributeur Last intervention -
Mytå Posted messages 4246 Registration date Status Contributeur Last intervention -
Hello everyone,
I don't know what manipulation I did to get the following result:
I have a table with 3000 rows.
But the scrollbar on the right takes me to row 1,045,160.
It's very difficult to navigate with the scrollbar. I'm using Ctrl + G, but I would like to get my sheet back to how it was before.
How can I return to normal display?
Thanks in advance.
I don't know what manipulation I did to get the following result:
I have a table with 3000 rows.
But the scrollbar on the right takes me to row 1,045,160.
It's very difficult to navigate with the scrollbar. I'm using Ctrl + G, but I would like to get my sheet back to how it was before.
How can I return to normal display?
Thanks in advance.
6 réponses
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.
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.
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.
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
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)]
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)]
Thank you for your reply
It didn't work.
Your method allowed me to select from the last row of my table up to row 1,048.57/
All the cells are now empty. I removed all the auto formatting, borders, and colors. They are all blank.
But no changes after saving, closing, and reopening.
A real mystery...
Have a good evening
Sorry, I forgot to mention that if you stopped at a certain line (1,048,571 in your case), you needed to press the down arrow key a second time while holding Ctrl + Shift to select "All" the lines (from 3,001 to 1,048,576), then right-click, "Delete," etc.....