Hello All,

How do I delete (for an entire worksheet) rows that contain "N"?

The cells are "B and "C".

If "B" and "C" contain the letter "N" delete the entire row?
The letter "N" is the only thing in the cell.

Hi,

You could put an auto filter on columns B & C and filter both for "N". The
results would be only the rows containing N in both B & C. Then highlight
the row numbers and press Ctrl - (Ctrl + Minus).

--
Thanks,
Shane Devenshire
Microsoft Excel MVP

"Red2003XLT" wrote:

Hello All,

How do I delete (for an entire worksheet) rows that contain "N"?

The cells are "B and "C".

If "B" and "C" contain the letter "N" delete the entire row?
The letter "N" is the only thing in the cell.

You can try this code:

Sub RemoveN()
' Macro recorded 4/30/2008 by Steven Bitaxi
'

Set Rng = Range("A1").CurrentRegion

' Assuming you have a header row
Set Rng = Rng.Rows(2).Resize(Rng.Rows.Count - 1)

Set Rng = Rng.SpecialCells(xlVisible)

' Removes rows with "N" in Field 2 (column B)
Selection.AutoFilter Field:=2, Criteria1:="=*N*"
Rng.Select
Selection.EntireRow.Delete
' Removes filter
Selection.AutoFilter Field:=2

' Removes rows with "N" in Field 3 (column C)
Selection.AutoFilter Field:=3, Criteria1:="=*N*"
Rng.Select
Selection.EntireRow.Delete
' Removes filter
Selection.AutoFilter Field:=3
End Sub


On Apr 30, 12:18*pm, Red2003XLT
wrote:
Is there an automated way? *I'm sure my users want to click and print.

Copy and paste the extract (rawdata-worksheet) then click and print



"ShaneDevenshire" wrote:
Hi,

You could put an auto filter on columns B & C and filter both for "N". *The
results would be only the rows containing N in both B & C. *Then highlight
the row numbers and press Ctrl - (Ctrl + Minus).

--
Thanks,
Shane Devenshire
Microsoft Excel MVP

"Red2003XLT" wrote:

Hello All,

How do I delete (for an entire worksheet) rows that contain "N"?

The cells are "B and "C". *

If "B" and "C" contain the letter "N" delete the entire row?
The letter "N" is the only thing in the cell.- Hide quoted text -

- Show quoted text -

Sub DeleteRows_2Params()
'Bob Phillips Aug. 26, 2006
'Revised Gord Dibben April 30, 2008
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "B").Value = "N" And _
Cells(i, "C").Value = "N" Then
Rows(i).Delete
End If
Next i
End Sub


Gord Dibben MS Excel MVP


On Wed, 30 Apr 2008 07:48:00 -0700, Red2003XLT
wrote:

Hello All,

How do I delete (for an entire worksheet) rows that contain "N"?

The cells are "B and "C".

If "B" and "C" contain the letter "N" delete the entire row?
The letter "N" is the only thing in the cell.