Reducing a For to loop

[lifeson]

I have a loop routine that loops through 20 rows of data and adds the results to a list view
Under certain conditions the loop will remove some rows of data when the routine does the next row how do I reduce the number of rows it searches? as the list view will still have the original 20 rows but has blank rows at the end

[vba]for i = 2 to r '20 rows

'do some stuff

Select Case chkComp
Case True
With listview
.ListItems.Add , , , , icon
End With
Case False
'MsgBox "Delete " & discID & " from row " & i
ws.Cells(i, "A").EntireRow.Delete

i = i - 1 'this doesn't reduce the number of rows from the original for loop
MsgBox r
End Select
Next i[/vba]

[Kenneth Hobs]

I would set a range from A2 down to the last cell in Column A that has a value or set the range from A2 to the last cell in Column A with a value by comming up from the last row.

One typically deletes rows from the bottom up. Once you have your data cleaned up, reset the range to add to your listbox. If you already have data in the listbox, use ListBox1.AddItem or use ListBox1.List=range if you are filling it initially.

If this sounds like what you need and need help, post back.

[RonMcK]

lifeson,

Try the following:[vba]For i = 2 To r '20 rows

'do some stuff

Select Case chkComp
Case True
With listview
.ListItems.Add , , , , Icon
End With
Case False
'MsgBox "Delete " & discID & " from row " & i
ws.Cells(i, "A").EntireRow.Delete

' i = i - 1 'this doesn't reduce the number of rows from the original for loop
r = r - 1 ' this should do the trick for you
MsgBox r
End Select
Next i
[/vba]

Cheers,