delete rows for values

J

Jake

Hello,
I have code that I hoped would iterate through a column and delete rows if
values did not begin with a 7.

For Each rngDBSingleCostElemCell In wksSheet.Range("C2:C" &
lCostElemItemLastRow)
If Left(rngDBSingleCostElemCell, 1) <> 7 Then
rngDBSingleCostElemCell.Rows.Delete
End If
Next rngDBSingleCostElemCell

The values in column C are numbers stored as text.
For some reason rows with values like 16101, 16151 remain.
thanks for any help.
Jake
 
R

Rick Rothstein

Doesn't that only work for cells formatted as General in which the number is
preceded with an apostrophe? I think cells formatted as Text will remain as
Text after doing what you suggested.
 
R

Rick Rothstein

I think you are falling victim to the removals that are taking place. When
you delete Row 1, all the other rows move up so that what was Row 2 is not
Row 1, etc. If Row2 had 16101 in it, that value would be in Row 1 after the
original Row 1 was deleted; but the For..Next loop already processed Row 1,
so it will not go back to it again. The way most people avoid this problem
is to iterate the loop row-by-row from the last row with data backwards to
the first row; that way, deletions do not affect the loop's processing
order. Try this (off the top of my head)...

With wksSheet
For X = lCostElemItemLastRow To 2 Step -1
If Left(.Cells(X, 1).Value, 1) <> 7 Then .Cells(X, 1).EntireRow.Delete
Next
End With
 
J

Jake

Thanks Rick! Workin' great now.

Rick Rothstein said:
I think you are falling victim to the removals that are taking place. When
you delete Row 1, all the other rows move up so that what was Row 2 is not
Row 1, etc. If Row2 had 16101 in it, that value would be in Row 1 after the
original Row 1 was deleted; but the For..Next loop already processed Row 1,
so it will not go back to it again. The way most people avoid this problem
is to iterate the loop row-by-row from the last row with data backwards to
the first row; that way, deletions do not affect the loop's processing
order. Try this (off the top of my head)...

With wksSheet
For X = lCostElemItemLastRow To 2 Step -1
If Left(.Cells(X, 1).Value, 1) <> 7 Then .Cells(X, 1).EntireRow.Delete
Next
End With
Click to expand...
 
Top