Deleting rows, multiple column criteria
 

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

--
Lift Of
-----------------------------------------------------------------------
Lift Off's Profile: http://www.excelforum.com/member.php...nfo&userid=824
View this thread: http://www.excelforum.com/showthread.php?threadid=50686

Deleting rows, multiple column criteria
 

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Lift Off" wrote in
message ...

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 the
row if "either" column "M" _or_ "N" is empty. I've tried changing the
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.


--
Lift Off
------------------------------------------------------------------------
Lift Off's Profile:
http://www.excelforum.com/member.php...fo&userid=8249
View this thread: http://www.excelforum.com/showthread...hreadid=506868

Deleting rows, multiple column criteria
 

hi Liff Off

Change Range("M:M") to

Range("M:N")

regards Yngve

Deleting rows, multiple column criteria
 

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

--
Lift Of
-----------------------------------------------------------------------
Lift Off's Profile: http://www.excelforum.com/member.php...nfo&userid=824
View this thread: http://www.excelforum.com/showthread.php?threadid=50686

Deleting rows, multiple column criteria
 

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

Deleting rows, multiple column criteria
 

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" wrote in
message ...

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

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

Any other ideas?

Cliff


--
Lift Off
------------------------------------------------------------------------
Lift Off's Profile:
http://www.excelforum.com/member.php...fo&userid=8249
View this thread: http://www.excelforum.com/showthread...hreadid=506868

Deleting rows, multiple column criteria
 

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

--
Lift Of
-----------------------------------------------------------------------
Lift Off's Profile: http://www.excelforum.com/member.php...nfo&userid=824
View this thread: http://www.excelforum.com/showthread.php?threadid=50686