-
Sep 3rd, 2021, 08:36 PM
#1
[RESOLVED] What can cause Excel to think I have over a million rows
I've posted similar questions before but haven't been able to solve the problem which is that while I only have about 6800 rows in my worksheet, Excel thinks the last row is 1,048,576! I know that formulas and formatting that were lazily added to whole columns can cause that, but I've cleared those and yet the usedrange doesn't change. I've also tried this trick
Code:
Sub ResetRng()
ActiveSheet.UsedRange
End Sub
but that didn't help. What am I missing?
-
Sep 4th, 2021, 03:52 AM
#2
Re: What can cause Excel to think I have over a million rows
1,048,576 is the last row on a sheet, so it appears that the entire sheet is being used
out of interest how many columns does it think you are using? 16384 is the number of columns on a sheet
i have done a few tests to try to make a demo, but without the problem occurring
do you want to post a sample to demonstrate the problem?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Sep 4th, 2021, 08:43 AM
#3
Re: What can cause Excel to think I have over a million rows
Here is the crazy worksheet. It's too big to attach directly. Disclaimer: I did not create the workbook of which this worksheet is a part.
https://www.mediafire.com/file/13hjg...Demo.xlsx/file
-
Sep 4th, 2021, 05:33 PM
#4
Re: What can cause Excel to think I have over a million rows
Based on this SO, usedrange is not reliable in finding the last used row and instead suggest this:
Code:
Private Sub GetTotal()
Dim lastrow As Long
With Sheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
End With
MsgBox lastrow, , "last row"
Rows(lastrow).Select
End Sub
-
Sep 4th, 2021, 05:51 PM
#5
Re: What can cause Excel to think I have over a million rows
Thanks but my problem is not finding the correct last row which I believe is row 6808, but rather that because Excel thinks the used range extends to row 1,048,576576
1) The size of the workbook is much larger than it needs to be and
2) Given that the last column is MT Excel thinks there are 375,390,208 (358 x 1048576) cells and any action that results in re-calculation takes a very long time.
-
Sep 4th, 2021, 11:04 PM
#6
Re: What can cause Excel to think I have over a million rows
i do not see where is the problem with the worksheet, but
you can try some variation of this code to fix the workbook
Code:
Sub dr()
Dim r As Range, ns As Worksheet
lr = Cells(Rows.Count, 6).End(xlUp).Row
Set r = Cells(1, 1).Resize(lr, Columns("mt").Column)
Set ns = Sheets.Add
r.Copy
With ns.Range("a1")
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteAll
End With
Application.DisplayAlerts = False
Sheets("sheet1").Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub
this appeared to work correctly and reduced the size of the workbook from 17Mb (downloaded) to less than 12Mb
test with care and saveAs
?ActiveSheet.usedrange.address
$A$1:$MT$6808
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Sep 4th, 2021, 11:42 PM
#7
Re: What can cause Excel to think I have over a million rows
Originally Posted by MartinLiss
Thanks but my problem is not finding the correct last row which I believe is row 6808, but rather that because Excel thinks the used range extends to row 1,048,576576
1) The size of the workbook is much larger than it needs to be and
2) Given that the last column is MT Excel thinks there are 375,390,208 (358 x 1048576) cells and any action that results in re-calculation takes a very long time.
You have a data at "CZ8669", if you are going to try the snippet I shared it will output 8669. I tried the code in the other workbooks you shared in your other threads, I encountered "1048576" as the result, upon checking, at least one of the cells in that row has a formula that is why it is being reported as the last row.
-
Sep 5th, 2021, 09:06 AM
#8
Re: What can cause Excel to think I have over a million rows
Thanks, but I know how to find the last row.
Concerning CZ8669. I didn't mention it in my question but in the workbook I attached I already cleared all formatting and formulas in all the rows following the last real data. You can verify that there are no formulas in row 8669 clicking 'Show Formulas' in the Formulas pane.
-
Sep 5th, 2021, 04:04 PM
#9
Re: [RESOLVED] What can cause Excel to think I have over a million rows
The problem was resolved by deleting the bottom rows in chunks and saving the file after each deletion.
-
Sep 6th, 2021, 01:42 AM
#10
Re: [RESOLVED] What can cause Excel to think I have over a million rows
Yeah, solved and all that, but i have the same issue here in the company i work for with our reporting tool.
Worksheet with 200 rows, but when i press CTRL+END it jumps to somewhere Row 16,987 or such (the row-numbers are just samples)
What i noticed: If you open the Sheet still in "faulty" mode, and save it once, it's ok afterwards, so i'm guessing the issue is actually not with Excel or the Sheet, but with the Tool generating that Workbook/Sheet.
Probably because the tool "misses" the native stuff for a Excel-Sheet to work properly out of the gates.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|