Delete Rows based on condition

Delete Rows based on condition

Post by Vmlj » Thu, 20 Aug 2009 02:31:02


How do I perform elimination of row based on the following:
If L2 = M2 then leave this row alone and check the next row
Else
if B2=C2 and D2=E2 and F2=G2 and H2=I2 and J2=K2 and N2=O2 and P2=Q2 delete
this entire row (all 7 conditions must be met to eliminate this row)
If any of 7 conditions fail then I need to keep this row.
Thank you.
 
 
 

Delete Rows based on condition

Post by THVrZSB » Thu, 20 Aug 2009 03:26:01

Here's a non-VB method:
You could create a hlper column with this formula copied down. Then do an
AutoFilter for "DELETE ME" and delete those rows.

=IF(AND(L2<>M2,B2=C2,D2=E2,F2=G2,H2=I2,J2=K2,N2=O2,P2=Q2),"DELETE ME","")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 
 
 

Delete Rows based on condition

Post by Otto Moehr » Thu, 20 Aug 2009 04:54:41

Something like this perhaps. HTH Otto
Sub DeleteRows()
Dim rColA As Range
Dim c As Long
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
For c = rColA.Count To 1 Step -1
If rColA(c).Offset(, 11) <> rColA.Offset(, 12) Then
If rColA(c).Offset(, 1) = rColA.Offset(, 2) And _
rColA(c).Offset(, 3) = rColA.Offset(, 4) And _
rColA(c).Offset(, 5) = rColA.Offset(, 6) And _
rColA(c).Offset(, 7) = rColA.Offset(, 8) And _
rColA(c).Offset(, 9) = rColA.Offset(, 10) And _
rColA(c).Offset(, 13) = rColA.Offset(, 14) And _
rColA(c).Offset(, 15) = rColA.Offset(, 16) Then _
rColA(c).EntireRow.Delete
End If
Next c
End Sub