Results 1 to 10 of 10

Thread: [RESOLVED] What can cause Excel to think I have over a million rows

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    Resolved [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?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    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
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    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.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: What can cause Excel to think I have over a million rows

    Quote Originally Posted by MartinLiss View Post
    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  8. #8

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,427

    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.

  9. #9

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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
  •  



Click Here to Expand Forum to Full Width