delete rows for values
 

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

delete rows for values
 

Hi,
Highlight column C and apply text to columns

"Jake" wrote:

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

delete rows for values
 

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.

--
Rick (MVP - Excel)


"Eduardo" wrote in message
...
Hi,
Highlight column C and apply text to columns

"Jake" wrote:

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

delete rows for values
 

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

--
Rick (MVP - Excel)


"Jake" wrote in message
...
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

delete rows for values
 

Thanks Rick! Workin' great now.

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jake" wrote in message
...
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