Deleting rows, multiple column criteria

[Lift Off]

I've been using this code to delete rows based upon data in one column.
How do I modify the code to include a second column? i.e. delete th
row if "either" column "M" _or_ "N" is empty. I've tried changing th
Range to ("M:M", "N:N") and it doesn't work.

On Error Resume Next
Intersect(Rows("2:" & Rows.Count)
Range("M:M").SpecialCells(xlBlanks, _
xlTextValues)).EntireRow.Delete
Intersect(Rows("2:" & Rows.Count)
Range("M:M").SpecialCells(xlConstants, _
xlTextValues)).EntireRow.Delete
Intersect(Rows("2:" & Rows.Count)
Range("M:M").SpecialCells(xlFormulas, _
xlTextValues + xlErrors + xlLogical)).EntireRow.Delete
On Error GoTo 0

Thanks for the help

 

[Bob Phillips]

Why not just repeat for column N, pick up the stragglers.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Yngve]

hi Liff Off

Change Range("M:M") to

Range("M:N")

regards Yngve

 

[Lift Off]

Yngve: I just tried that, thought it should work but it leaves row
only if BOTH column M and N have data. I want to leave the row i
EITHER column has data.

Bob: Not sure I understand. After the first pass only rows with dat
in column M are left. It clears rows with data in N.

Any other ideas?

Clif

 

[Yngve]

hi Lift Off
this sub keeps the rows with non values in M/N and values in M/N
if not this help, twist it around.
Sub ss()
Sheets("ppp").Select
Dim i As Double
i = Cells(Rows.Count, 1).End(xlUp).Row
For i = i To 6 Step -1
If Range("M" & i) > "" And Range("N" & i) <= "" _
Or Range("N" & i) > "" And Range("M" & i) <= "" Then
' do nothing
Else
Range("M" & i).EntireRow.Delete

End If
Next i
End Sub

regards yngve

 

[Bob Phillips]

This is what I mean

On Error Resume Next
Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlBlanks, _
xlTextValues)).EntireRow.Delete
Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlConstants, _
xlTextValues)).EntireRow.Delete
Intersect(Rows("2:" & Rows.Count), Range("M:M").SpecialCells(xlFormulas, _
xlTextValues + xlErrors + xlLogical)).EntireRow.Delete
On Error GoTo 0

On Error Resume Next
Intersect(Rows("2:" & Rows.Count), Range("N:N").SpecialCells(xlBlanks, _
xlTextValues)).EntireRow.Delete
Intersect(Rows("2:" & Rows.Count), Range("N:N").SpecialCells(xlConstants, _
xlTextValues)).EntireRow.Delete
Intersect(Rows("2:" & Rows.Count), Range("N:N").SpecialCells(xlFormulas, _
xlTextValues + xlErrors + xlLogical)).EntireRow.Delete
On Error GoTo 0

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Lift Off]

Bob: Tried duplicating. Sheet has 35K rows. After running the firs
line of code below, using just column M, it deletes all rows wit
blanks in column M. Deletion includes rows with data in N if they wer
blank in M.

'Intersect(Rows("2:" & Rows.Count)
Range("M:M").SpecialCells(xlBlanks, _
xlTextValues)).EntireRow.Delete

The only rows that are left after running the total code are rows wit
data in column M _and_ N. Looking for code that'll leave rows wit
data in column M _or_ N.

yngve: Couldn't get the code to run. It locked up "as is". Change
the 'step' from -1 to 1 and it doesn't lock up, but runs throug
without deleting any rows.

Thanks, Clif