vba - Excel.Application.Cells.SpecialCells(xlCellTypeLastCell) returning bottom of worksheet, not last data cell

Question

I'm writing a method in VBA in Excel 2013 to loop through the rows in two worksheets and compare the text in a column from each. When I run my code, I find that the code loops through the entire worksheet, not just the rows with data.

Excel.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell) returns a cell in the correct column, but the row is the last row in the sheet (1048576), rather than the last row with data (1951).

I had written a check for empty cells (since I can't be sure that every row in the valid range is used), so it doesn't cause any errors, but as this method is called from inside Worksheet_Change, it really slows things down.

Normally when the "last" cell is reported incorrectly a save usually fixes it, and if that doesn't, then deleting the rows (not just the contents, but the entire rows) from the mis-reported last cell back to the actual last cell and then saving works. However, in this instance, it is not helping.

I searched with Google without success. I'd like to not have to copy all the data and code out of this workbook and into a new one. Any ideas?

Answer 1

(Too much info to use a comment here.)

VBA mastermind Ron de Bruin wrote a little snipped about why xlCellTypeLastCell as well as UsedRange might be failing here: http://www.rondebruin.nl/win/s9/win005.htm.

(In the post I linked to in my initial comment, Error in finding last used cell in VBA, the pitfalls of UsedRange are described in the same way.)

Here's the direct quote:

Possible problems with xlCellTypeLastCell and UsedRange are:

The last cell will only re-set when you save (or save/close/reopen the file). If cell formatting is changed it will not reset the last cell, clearing the data is not enough, you must delete the rows or columns then, See: http://www.contextures.com/xlfaqApp.html#Unused

To make a long story short, the logic for finding the last row on a sheet belongs inside a global function. You will use this function all the time. Here's an example for finding the last row on a sheet:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
'EXAMPLES    :
' 
'assume that there is a single 
'entry on MySheet in cell A5:
'
'LastRowNum(MySheet)
'>> 5
'
'assume that EmptySheet is totally empty:
'
'LastRowNum(EmptySheet)
'>> 1
'
Public Function LastRowNum(Sheet As Worksheet) As Long
    If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
        LastRowNum = Sheet.Cells.Find(What:="*", _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious).Row
    Else
        LastRowNum = 1
    End If
End Function

As far as the downvote goes -- no idea. I've actually never downvoted here haha.