Looping through rows

krice1974

Active Member
Joined
Jul 3, 2008
Messages
422
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
Code:
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
 
Upvote 0
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.

Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg
Untitled%20picture.jpg

Untitled%20picture.jpg
 
Upvote 0
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.
 
Upvote 0
You shouldn't be subtracting 8 form finalrow, plus final row and i need to be declared as Long. Try this

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top