Looping through rows

[krice1974]

Hey there all, I'm working out of the book, and I'm close to what I need but at a beginner's block. I want to delete all rows of a table of stock information where the symbol (column b) contains a "." Here's what I've got:

Sub Earnings_SymbsClnUp()
'Deletes the newly posted trades that have a "." in the symbol.

Dim finalrow As Integer


Dim i As Integer
finalrow = Cells(Rows.Count, 1).End(xlUp).row - 8
MsgBox finalrow

For i = finalrow To 9 Step -1
If Cells(i, 2).Value = "ITRA" Then
Cells(1, 2).EntireRow.delete
End If
Next i

End Sub

 

[krice1974]

Sorry, somehow tab posted that first one early. So, my table starts at row 9. I have conditional formatting on the column for "text that contains " a "." to highlight red. This is what I've got rough so far:

Sub Earnings_SymbsClnUp()
'Deletes the newly posted trades that have a "." in the symbol.

Dim finalrow As Integer
Dim i As Integer

finalrow = Cells(Rows.Count, 1).End(xlUp).row - 8
For i = finalrow To 9 Step -1
If Cells(i, 2).interior.ColorIndex = "2" Then
Cells(i, 2).EntireRow.delete
End If
Next i

End Sub

Thanks in advance for the help for a newbie. This is fascinating stuff.

 

[xld]

Sub Earnings_SymbsClnUp()
'Deletes the newly posted trades that have a "." in the symbol.

Dim finalrow As Long
Dim i As Long

    finalrow = Cells(Rows.Count, 1).End(xlUp).Row - 8
    For i = finalrow To 9 Step -1
        If InStr(Cells(i, 2).Value, ".") > 0 Then
            Rows(i).Delete
        End If
    Next i

End Sub

 

[krice1974]

Thanks, but I'm having the same problem with that as my last attempt at looping. Running that deletes some, but not all of the rows whos symbol contains a ".". And it blows through and deletes rows in the 1-8 range which are outside the table area.

 

[krice1974]

BTW forgive the image addresses, I'm new.

 

[xld]

Is that suppoed to be examples of what is in the cells?

 

[krice1974]

No, I was trying to paste before running macro/after running macro screen shots.

 

[xld]

Well, perhaps you could explain it in words if that failed.

 

[krice1974]

OK, here's what I've got for code now:

Sub Earnings_SymbsClnUp()

'Deletes the newly posted trades that have a "." in the symbol.

Dim finalrow As Integer
Dim i As Integer
finalrow = Cells(Rows.Count, 1).End(xlUp).row - 8
MsgBox finalrow

For i = finalrow To 9 Step -1
If InStr(Cells(i, 2).Value, ".") > 0 Then
Cells(1, 2).EntireRow.delete
End If
Next i

End Sub

So, the upper 8 rows are not part of the table. But when I run this it doesn't delete all of the ones to be deleted, yet it fries the upper rows. I wish I could paste a jpeg in here.

 

[VoG]

You shouldn't be subtracting 8 form finalrow, plus final row and i need to be declared as Long. Try this

Sub Earnings_SymbsClnUp()

'Deletes the newly posted trades that have a "." in the symbol.

Dim finalrow As Long
Dim i As Long
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
'MsgBox finalrow

For i = finalrow To 9 Step -1
If InStr(Cells(i, 2).Value, ".") > 0 Then
    Rows(i).EntireRow.Delete
End If
Next i

End Sub