Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Last Row Used question 1

Status
Not open for further replies.

mscallisto

Technical User
Jun 14, 2001
2,990
US
Code:
' 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
 
And what about this ?
intLastRow = ActiveSheet.[!]UsedRange[/!].SpecialCells(xlCellTypeLastCell).Row

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
Thx PHV

Of course I know that !!!

Cutting and pasting will get you every time!!

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top