syntax help needed 2

[mscallisto]

I'm stuck on syntax and need some assistance

Sub CleanUpData()

Dim intLastRow As Integer
intLastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
   
'   Delete all un-needed rows
For Ii = 1 To intLastRow - 1 
  If Rows(Ii).Columns(5).Interior.ColorIndex <> 5 Then
     [Red]'I want to delete from this row forward 
     'and the following 2 lines of code don't work.[/red]
     [b]Rows(Ii).Select
     Selection.Delete[/b]
     '[red]Plus I really don't want to continue looping but simply:
     'Rows(Ii:toLastRow).delete and exit the loop[/red] 

  End If
Next Ii

 

[RonRepp]

Try:

For Ii = 1 To intLastRow - 1 
  If Rows(Ii).Columns(5).Interior.ColorIndex <> 5 Then

     Rows(Ii:toLastRow).Select
     Selection.Delete (xlUP) 'Shift direction
     Exit For

  End If
Next Ii

This should now catch the first row with #5 colorindex and delete to the end of the rows.

I hope this helps


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[PHV]

What about this ?
For Ii = 1 To intLastRow - 1
If Rows(Ii).Columns(5).Interior.ColorIndex <> 5 Then
Rows(Ii & ":" & toLastRow).Delete xlUP
Exit For
End If
Next Ii

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

 

[bdmangum]

If you're only checking column 5, try something like the code below.

Dim RowC As Long, check As Long
RowC = 1

'Gets to last colored row
While ActiveSheet.Cells(RowC, 5).Interior.ColorIndex = 5
  RowC = RowC + 1
Wend

'Deletes all the following rows, assuming each row has data
While ActiveSheet.Cells(RowC, 5).Value <> "" And check <> 65536 - RowC
    ActiveSheet.Rows(RowC).Delete
    check = checkl + 1
Wend

 

[mscallisto]

Thanks guys

For me sometimes syntax is tougher than sin tax !!

sam