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

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

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" <(E-Mail Removed)> wrote in message
news:7015D7B2-3A98-4701-AC65-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:F15F320A-EFBE-4332-B67A-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:F15F320A-EFBE-4332-B67A-(E-Mail Removed)...
> > 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
>
>