Please help!! Easy question

ericam

New Member
Joined
Jul 24, 2005
Messages
20
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!!!!!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

New Member
Joined
Jul 24, 2005
Messages
20
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

New Member
Joined
Jul 24, 2005
Messages
20

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top