delete rows on value

[Guest]

Hi...

When I use the code below it doesn't work. I want to delete any row where
the an N is found in column C. Can anyone fix this for me. The problem is
that it only deletes a few at a time and I don't know why.

Sub Delete_N_MarkedRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If UCase(Cells(r, 3).Value) = "N" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[Guest]

Assumng the code is in a general/standardd module, the code looks good. If
it isn't deleting the rows, then there is more than the value N in the missed
rows. Perhaps using

Sub Delete_N_MarkedRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If Instr(1,Cells(r, 3).Value,"N",vbTextcompare) Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

or use Ucase(trim(cells(r,3)) in your original code it the culprit could be
spaces.

the correct answer depends on what is in the cells - knowledge only
available to you.

 

[Guest]

Hi Tom...

The cells in column c contain either an N or a zero (0). I tried that code
but it didn't stack up and just deleted the top line...

Any help would be fab...

Gordon...

 

[Tom Ogilvy]

In contrast, I ran that code on a worksheet with "either an N or a zero (0)"
in column C and it worked flawlessly. I don't think it is the code that
doesn't stack up. Thus, I don't think there is any help I can offer, since
as I said, it isn't the code (which works exactly as expected) and that is
all that is on the table here.