Please help!! Easy question

[ericam]

I have about 20 columns in Excel. In column B there are some entries in the cells that say N, some that say Y, and some that say YN. I want a code please that could delete all the rows that have an N on it AND the row before it. For instance:

Y Y 34 56 67 89
23 23 56 89
Y N 45 89 11 09
22 45 6 77
Y YN 44 55 77 98

should look like this:

Y Y 34 56 67 89
22 45 6 77
Y YN 44 55 77 98

Thanks Very much Guys Please help!!!!!

 

[Tom Urtis]

One way:


Sub Test1()
Dim FindWhat$, cell As Range, FindRange As Range
FindWhat = "N"
If Application.CountIf(Columns(2), FindWhat) = 0 Then Exit Sub
With Application
.ScreenUpdating = False
For Each cell In Columns(2).SpecialCells(2)
If cell.Value = FindWhat Then
If FindRange Is Nothing Then
Set FindRange = Range(cell, cell.Offset(-1, 0))
Else
Set FindRange = .Union(FindRange, Range(cell, cell.Offset(-1, 0)))
End If: End If: Next cell
FindRange.EntireRow.Delete
Set FindRange = Nothing
.ScreenUpdating = True
End With
End Sub

 

[ericam]

Hi Tom,
I appreciate you telling me. Is it possible you can fill in the necessary information because i don't know how?
Thank you very much!!!

 

[Tom Urtis]

What necessary information? The code does what you asked for as I understood your question, quoting:

"I want a code please that could delete all the rows that have an N on it AND the row before it."

The code worked when I tested it with your example data. If there's something else to the issue, I must have missed the point, not sure what else you'd need.

 

[ericam]

Hi Tom,
I pasted this:
Set FindRange = .Union(FindRange, Range(cell, cell.Offset(-1, 0)))
End If: End If: Next cell
FindRange.EntireRow.Delete
Set FindRange = Nothing
.ScreenUpdating = True
End With
End Sub

and it didnt work and i also tried this:

Sub Test1()
Dim FindWhat$, cell As Range, FindRange As Range
FindWhat = "N"
If Application.CountIf(Columns(2), FindWhat) = 0 Then Exit Sub
With Application
.ScreenUpdating = False
For Each cell In Columns(2).SpecialCells(2)
If cell.Value = FindWhat Then
If FindRange Is Nothing Then
Set FindRange = Range(cell, cell.Offset(-1, 0))

and it didnt work. Is there something else i should be doing?
Thanks

 

[Tom Urtis]

Yes there is something else you should be doing, which is to paste into a standard VBA module the entire code I posted as one contiguous macro because that is what it is.

 

[ericam]

Thanks Tom
Works perfectly