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?
(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.