ericam

New Member
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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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

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!!!

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.

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

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.

Thanks Tom
Works perfectly

Replies
2
Views
363
Replies
1
Views
198
Replies
6
Views
234
Replies
5
Views
239
Replies
3
Views
96

1,217,764
Messages
6,138,471
Members
450,141
Latest member
Hal5000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back