Solved: deleting rows matching criteria from a column

[AleemAM123]

Hi Everyone,

I need some help again. I have a sheet where in column C there are entries "Reduction" or "Original". I need to delete rows which have the entry "Reduction" and all other rows which had the same number in Column A.

There may be other entries beside ?riginal or reduction in column C e.g. commiment.

Thanks in advance for any help.

[Bob Phillips]

Do you mean delet all rows where the column A value equals any where the column B value is Reduction?

If so, try



Sub DeleteData()
Dim rng As Range
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("G2").Resize(LastRow - 1).Formula = "=OR(C2=""Reduction"",SUMPRODUCT(--($A$2:$A$" & LastRow & "=A2),--($C$2:$C$" & LastRow & "=""Reduction""))>0)"
.Columns("G:G").AutoFilter fielD:=1, Criteria1:="=TRUE"
Set rng = .Range("G2").Resize(LastRow - 1).SpecialCells(xlCellTypeVisible)
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
End Sub

[AleemAM123]

your code is doing exactly what i need it to do in the sample but it's not working out in the actual file, maybe because column c can contain other text beside ?riginal" or "reduction".

the actual file will also have more columns and over 7000 rows. I had replaced your reference to column g in the code to column m but i think excel is going into some kind of infinite loop?

this is what the actual file would contain (see attachment), i had thought that what i had provided initially would have been an adequate example, sorry bout that.

in this example all items with number 10225564 should be deleted cause there is a reduction entry.

all items with number 99999999 should be left cause there is no reduction entry.

[mdmackillop]

Option Explicit
Sub DelReduction()
Dim cel As Range
Dim i As Long, j As Long
j = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To j
If Cells(i, 3) = "Reduction" Then
Columns(1).AutoFilter Field:=1, Criteria1:=Cells(i, 1)
Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
i = 2
j = Cells(Rows.Count, 1).End(xlUp).Row
End If
Next
Columns(1).AutoFilter
End Sub

[AleemAM123]

Wow! mdmackillop that works great. I had found a way to do it manually using autofilter and vlookup and the results matches your method exactly. Thanks a lot.