Remove whole column if (multiple conditions)

[Archived-1715]

In column A I have ID
column C and E I have Type #'s
in G I have comments.

I need to remove whole row or row's if ID repeats
example

if C=13 & D=83 in same row then delete whole row and delete whole row where ID is same so that would be remove all rows where ID = 12

second condition is if column G contains text "Found OK" or "Found Oky" then remove whole row and if ID repeats then remove all rows where ID repeats example 14.

I already applied conditional formatting in G. Highlight it red if it contains Found Ok or Found Oky or whatever filter I need.
if cells in column G are red then remove that row would be ok solution also.

Thanks

 

[Archived-1715]

I was able to find code which remove all rows if cell is highlighted red
now I just need help removing if C=13 & D=83 then delete whole row and if that repeats then delete that entire row also

Sub deleterow()
Dim cell As Range, DelRange As Range


For Each cell In ThisWorkbook.Sheets("Sheet1").Range("G:G")
If cell.Interior.ColorIndex = 38 Then
If DelRange Is Nothing Then
Set DelRange = cell
Else
Set DelRange = Union(DelRange, cell)
End If
End If
Next cell
If Not DelRange Is Nothing Then DelRange.EntireRow.Delete
End Sub

 

[montecarlo2079]

well here is one way. this adds a formula to column H to indicate whether that row meets the criteria.. it then puts "DELETE" next to the items that meet it.

1. 
   
   This code will insert copy the formula down until column H is empty
   
   Sub copyformula()
   Range("H2").Select
   ActiveCell.FormulaR1C1 = "=IF(AND(RC[-5]=13,RC[-4]=83),""DELETE"","""")"

Range("h2").Copy

Range("h3:h" &
Cells(Rows.Count,"g").End(xlUp).Row).PasteSpecial

Application.Calculation= xlCalculationAutomatic

1. 
   CALL EXA1
   
   END SUB

Sub exa1()
Dim lLRow As Long

With ThisWorkbook.Worksheets("SHEET NAME")
lLRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("H:h").AutoFilter Field:=1, Criteria1:="DELETE"
.Range("h2:h" & lLRow).SpecialCells(xlCellTypeVisible).EntireRow.delete xlShiftUp
.AutoFilterMode = False
End With
End Sub

 

[Archived-1715]

thanks but for example 2nd post I made with image
how can I delete row 2? I want to delete that also because 55 in row1 contains 13 & 83?

 

[montecarlo2079]

ADVERTISEMENT

if you put that formula in h2 and copy it down, it should work.

 

[Philosophaie]

This macro should get you started:

Sub Delete()
For k = 1 To Cells(Rows.Count, 3).End(xlUp).Row
If Cells(k, 3) = 13 And Cells(k, 5) = 83 Then
cola = Cells(k, 1)
Rows(k & ":" & k).Delete Shift:=xlUp
For h = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(h, 1) = cola Then Rows(h & ":" & h).Delete Shift:=xlUp
Next h
End If
Next k
End Sub

 

[Archived-1715]

I don't know if I am doing it wrong or not.
so it put DELETE in column H for row 2, 6 and 17 and it only deletes those and not the rest like 3,4,5 where ID=12
and doesn't delete row 18 where ID=27.

I need to delete those also.
Thanks

 

[montecarlo2079]

you said you only want it to delete rows where type 1 = 13, and type 2 = 83.

if row 3 has type = 12, then it wont delete those.