Excel thinks I have over a million used rows

  • I have a workbook where if you press Ctrl+End, Excel will highlight MT1048576 as the last used cell when actually the last used cell is CZ8669. I know that formatting and formulas in cells "count" as being used but I have cleared all formatting and formulas in the rows below 8669 and it didn't help. I also tried the trick below but it didn't help either. Does anyone know what's going on?


    Because of the problem the workbook is too large to attach here so here is a download link.

    Problem workbook

    Code
    Sub ResetRng()
    ActiveSheet.UsedRange
    End Sub
  • You need to Delete (not only Clear) all rows and columns after the last valid cell.

    For rows: Select first empty row, hold Shift, tap End then Down Arrow, release Shift, apply Delete

    For columns: Select first empty column, hold Shift, tap End then Right Arrow, release Shift, apply Delete

  • That does work! My problem is that if I try to delete hundreds of thousand of rows at the same time, my Excel runs into memory problems so I either need to manually do it in a bunch of chunks or create a macro to do it for me.

  • You're more than welcome. I struggled with this problem off and on for a couple of months or more giving up each time in frustration. With your solution I was able to delete the bottom rows in chunks (necessitated by my memory problems), saving the file after each deletion. The last used row is now 6808!


    I not very familiar with this forum so if there's any way I can get you more points or other type of recognition, please let me know.

  • Points and trophies are arranged automatically by the Forum, all you can do is use "Likes" for the best posts.

    You asked for a macro to do the dirty work, I usually use this one to be found here: Link

    Never test on original files.

  • Try this


    Code
    Sub ResetLastRow()
        Dim oWS As Worksheet
        Dim L As Long
    
        For Each oWS In ActiveWorkbook.Worksheets
            L = oWS.UsedRange.Rows.Count
        Next
        
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!