Last Row Used question 1

[mscallisto]

' Delete all rows after the last light blue in col 5
' Rows are sorted by col 5 color

For intPtr = 2 To intLastRow - 1 ' Skip Header Row
  If Rows(intPtr).Columns(5).Interior.ColorIndex <> "20" Then
     Rows(intPtr & ":" & intLastRow).Delete xlUp
     Exit For
  End If
Next intPtr
intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
[\code]

The above code works fine, it deleted all remaining rows after the last light blue in col 5

I expected the variable intLastRow to be recalculated after the deletes, but it retained it's orig value.  
Why is this?

I temporarily fixed the problem with:
intLastRow = intPrt -1

 

[PHV]

And what about this ?
intLastRow = ActiveSheet.[!]UsedRange[/!].SpecialCells(xlCellTypeLastCell).Row

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AnotherHiggins]

faq707-2112
or
faq707-2115

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mscallisto]

Thx PHV

Of course I know that !!!

Cutting and pasting will get you every time!!