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

[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)

"Lift Off" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Lift Off]

Bob: Tried duplicating. Sheet has 35K rows. After running the first line of code below, using just column M, it deletes all rows with blanks in column M. Deletion includes rows with data in N if they were 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 with data in column M and N. Looking for code that'll leave rows with data in column M or N.

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

Thanks, Cliff

[Norwaydude]

Try

Sub test()

For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) = Blank And Cells(i, 2) = Blank Then Cells(i, 1).EntireRow.Delete
Next

End Sub

[Norwaydude]

Sub test()

For i = Cells(Rows.Count, 14).End(xlUp).Row To 1 Step -1
If Cells(i, 14) = Blank And Cells(i, 13) = Blank Then Cells(i, 14).EntireRow.Delete
Next i

End Sub
------
This is for Colum 13 and 14 (normally Colum M and N)