SpecialCells(xlLastCell) doesn't update after deleting rows

Question

I have the following vba code:

StartLastRow = .Cells(1, 1).SpecialCells(xlLastCell).Row

If StartLastRow > 24 Then
    .Cells(StartLastRow, 1).CurrentRegion.EntireRow.Delete
    StartLastRow = .Cells(1, 1).SpecialCells(xlLastCell).Row
End If

After deleting rows the value of StartLastRow remains the same.
How can I fix it?

Use `.Find`. See [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) — Siddharth Rout, Nov 29 '17 at 10:45
You could try adding: `Debug.print .UsedRange.Rows.Count` before resetting the variable. — Rory, Nov 29 '17 at 10:51
saving workbook resets this, but as @SiddharthRout wrote, this is not the best way — avb, Nov 29 '17 at 11:16
@SiddharthRout I know that saving solves but I won't save while running the macro — genespos, Nov 29 '17 at 11:21

score 1 · Answer 1 · answered Nov 29 '17 at 11:21

Following @Siddharth Rout comment above, using the Find function is the safest way to get the last row.

StartLastRow = FindLastRow

If StartLastRow > 24 Then
    .Cells(StartLastRow, 1).CurrentRegion.EntireRow.Delete
    StartLastRow = FindLastRow
End If

'=======================================================================

Function FindLastRow() As Long
    ' This Function finds the last row in a worksheet, and returns the row number
    Dim LastCell As Range
    Set LastCell = Cells.Find(What:="*", After:=Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        FindLastRow = LastCell.Row
    Else
        MsgBox "Error! worksheet is empty", vbCritical
        FindLastRow = -10000
    End If

End Function

Thanks, but this is a workaround and I prefer to use another sheet (I can in this case). Anyway I appreciate (+1) — genespos, Nov 29 '17 at 17:16

SpecialCells(xlLastCell) doesn't update after deleting rows

1 Answers1

Linked