Jumping to the Real Last Cell

Re. my most recent comment below, these changes will make the ClearEmpties3 macro run faster.nReplace this statementn    If Len(Trim(ConvUniSpaces(WorksheetFunction.Clean(c.Value)))) = 0 Thennwith this statementn    If Len(RemoveUniSpaces(WorksheetFunction.Clean(c.Value))) = 0 Thennand replace the ConvUniSpaces function with the following function:nnFunction RemoveUniSpaces(Text As String) As Stringn    Const UniSpaces = "\s|[\u00A0\u2000-\u200A\u202F\u205F\u3000]"n    With CreateObject("VBScript.RegExp")n        .Pattern = UniSpaces: .Global = Truen        RemoveUniSpaces = .Replace(Text, vbNullString)n    End WithnEnd FunctionnnIt might be necessary to use VB Editor's Tools menu to Reference the Microsoft VBScript Regular Expressions 5.5 library. The latest version of the VBA library includes RegExp; in this case, you can replace this late binding statementn    With CreateObject("VBScript.RegExp")nwith this statementn    With New RegExpnfor early binding, but the macro will probably fail when using Excel 2024 or an earlier version.

@Alex BlakenburgnThank you for your comment. I was not aware the SpecialCells problem was fixed in Excel 2010. Here's another version of the macro that addresses merged cells and Unicode space characters plus status bar display.nnSub ClearEmpties3()n        Dim c As Range, J As Long, K As Long, sSB As String, bDSB As Booleann        ActiveSheet.UsedRange.Selectn        On Error Resume Nextn                Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Selectn                If Err Then Exit Subn        On Error GoTo 0n        sSB = Application.StatusBarn        bDSB = Application.DisplayStatusBarn        Application.DisplayStatusBar = Truen        K = Selection.Cells.Countn        For Each c In Selectionn                J = J + 1: Application.StatusBar = J & " of " & Kn                If Len(Trim(ConvUniSpaces(WorksheetFunction.Clean(c.Value)))) = 0 Thenn                        c.MergeArea.Clearn                End Ifn        Next cn        Application.StatusBar = IIf(sSB = "FALSE", False, sSB)n        Application.DisplayStatusBar = bDSBn        ActiveSheet.UsedRange.SelectnEnd SubnnFunction ConvUniSpaces(Text As String) As Stringn        Static UniSpaces As Variant, n As Integern        If IsEmpty(UniSpaces) Thenn                UniSpaces = Array(ChrW(&HA0), "NO-BREAK SPACE", _n                        ChrW(&H2000), "EN QUAD", _n                        ChrW(&H2001), "EM QUAD", _n                        ChrW(&H2002), "EN SPACE", _n                        ChrW(&H2003), "EM SPACE", _n                        ChrW(&H2004), "THREE-PER-EM SPACE", _n                        ChrW(&H2005), "FOUR-PER-EM SPACE", _n                        ChrW(&H2006), "SIX-PER-EM SPACE", _n                        ChrW(&H2007), "FIGURE SPACE", _n                        ChrW(&H2008), "PUNCTUATION SPACE", _n                        ChrW(&H2009), "THIN SPACE", _n                        ChrW(&H200A), "HAIR SPACE", _n                        ChrW(&H202F), "NARROW NO-BREAK SPACE", _n                        ChrW(&H205F), "MEDIUM MATHEMATICAL SPACE", _n                        ChrW(&H3000), "IDEOGRAPHIC SPACE")n        End Ifn        ConvUniSpaces = Textn        If Len(ConvUniSpaces) > 0 Then 'replace UniSpaces with ASCII spacen                Const A_SPACE = " "n                For n = LBound(UniSpaces) To UBound(UniSpaces) Step 2n                        ConvUniSpaces = Replace(ConvUniSpaces, UniSpaces(n), A_SPACE)n                Next nn        End IfnEnd Function

We don't really have enough information to provide a robust code solution. With the data being "imported" formatting is unlikely to be an issue. Neither macro handles non-breaking spaces (ASCII 160) but that also seems unlikely for an imported file. Both macros will fail if there are merged cells, again unlikely for an imported file.nnA non-US regional setting could impact dates in the original macro provided but unlikely to impact numbers (they will stay as numbers despite the use of Trim).nLooping through almost 50k rows 1 cell at a time is not going to be quick. if there a lot of empty cells sprinkled through the 50k rows, you will wind up with a large number of non-contiguous cells in the selection.n@J. Woolley - Re: 8,192 non-contiguous ranges (Areas) limit.nRefer to:nhttps://jkp-ads.com/rdb/win/s4/win003.htmn"Note: This problem is fixed in Excel 2010nNon-contiguous cells that can be selected in Excel 2010 : 2,147,483,648 cells"nnMind you I tested it on around 30k areas and the macro gets really ... slow as the number of non-contiguous ranges increases.

The Tip's ClearEmpties macro starts with the following statement:n        Selection.SpecialCells(xlCellTypeConstants, 23).SelectnThe result will be unreliable unless the user selects all cells (Ctrl+A) before running the macro. And the macro has some undesirable side effects for cells that contain a text constant:n1. If the text is numeric it is converted to a number (non-text).n2. If the text is a date it is probably converted to a number (non-text).n3. If the text has leading or trailing space characters they are removed.nThese side effects become permanent if the workbook is saved. Finally, cells that contain only non-printing characters and/or line breaks (with or without space characters) should be cleared but are not.nHere's an alternate version that resolves these issues:nnSub ClearEmpties2()n        Dim c As Rangen        ActiveSheet.UsedRange.Selectn        For Each c In Selection.SpecialCells(xlCellTypeConstants, xlTextValues)n                If Len(Trim(WorksheetFunction.Clean(c.Value))) = 0 Then c.Clearn        Next cn        ActiveSheet.UsedRange.SelectnEnd SubnnClearEmpties2 ignores the StatusBar because it runs too quickly for the user to notice. ActiveSheet.UsedRange updates the last used cell, so it is not necessary to save the workbook in order to reset Ctrl+End.nWarning: The SpecialCells method fails silently if it identifies more than 8,192 non-contiguous ranges (Areas), but this is unlikely.

A couple of points about the "simple" answer :-nnThere is no need to get out of Excel and reopen the workbook. nAfter saving, you can keep the workbook open and immediately test the location of the last cell with Ctrl-End.nnThis resetting of the last cell automatically works for EVERY worksheet in the workbook, not just the active worksheet.nThere is no need to repeat the process for the other worksheets.

FYI - It is the Save function of the workbook that recalculates the UsedRange on which the Ctrl+End relies. So after the macro Ctrl+End should change after you hit Save. There should be no need to Close and ReOpen the workbook.nnPS: Also both references to the StatusBar should say Application.StatusBar (with Option Explicit set - the current code errors out)