Delete rows with reference

[Sathisc]

Hi,

I have a data consists of 3 column as given below

A	B	C
10252554	SEA	C
10252554	LSF	MC
10252554	DGP	MC
10252554	BOO	MC
10252554	TSD	BTO
10252554	THD	BTO
10252548	SEA	P
10252548	LSF	MC
10252548	DGP	MC
10252548	BOO	MC
10252548	TSD	BTO
10252548	THD	BTO

<tbody>
</tbody><colgroup><col span="3"></colgroup>

scenario - If Column B consists of "SEA" and Colum C with "C" the rows(entire) which has the number 10252554 needs to get deleted.

Help is much appreciated

Output required

A	B	C
10252548	SEA	P
10252548	LSF	MC
10252548	DGP	MC
10252548	BOO	MC
10252548	TSD	BTO
10252548	THD	BTO

<tbody>
</tbody><colgroup><col span="3"></colgroup>

 

[Joe4]

One question that will help me formulate a plan...
Is there any possibility for duplicates with the "SEA/C" combination?
Meaning, could you have two (or more) rows that have "SEA/C/10252554"?
If there is no possibility of duplicates, it will make it much easier.

 

[Sathisc]

One question that will help me formulate a plan...
Is there any possibility for duplicates with the "SEA/C" combination?
Meaning, could you have two (or more) rows that have "SEA/C/10252554"?
If there is no possibility of duplicates, it will make it much easier.

the combination will appear only once and there is no duplication

 

[Joe4]

Try this:

Sub MyDeleteRows()

    Dim rng As Range, rngVisible As Range
    Dim rCell As Range, myArray() As Variant, i As Long
    Dim RowCt As Long
    
    Application.ScreenUpdating = False
    
'   Get range of data
    Set rng = Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
    
'   See how many rows have "SEA" in column B and "C" in column C
    RowCt = Application.WorksheetFunction.CountIfs(Range("B:B"), "SEA", Range("C:C"), "C")
    
'   If RowCt>0 then delete rows with column A = 10252554
    If RowCt > 0 Then
'       Filter out records with "SEA" in column B and "C" in column C
        rng.AutoFilter Field:=2, Criteria1:="SEA"
        rng.AutoFilter Field:=3, Criteria1:="C"
'       Loop though visible rows storing codes from column A in array
        With rng
            Set rngVisible = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)
        End With
        For Each rCell In rngVisible
            i = i + 1
            ReDim Preserve myArray(1 To i)
            myArray(i) = rCell
        Next rCell
'       Loop through array to build criteria
        For i = LBound(myArray) To UBound(myArray)
'           Filter data
            ActiveSheet.AutoFilter.ShowAllData
            rng.AutoFilter Field:=1, Criteria1:=myArray(i)
'           Delete visible data after filter
            Application.DisplayAlerts = False
            ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
            Application.DisplayAlerts = True
'           Remove filters
            ActiveSheet.AutoFilter.ShowAllData
            Next i
    End If
    
    Application.ScreenUpdating = True
    
End Sub

 

[Peter_SSs]

Here is another option to try in a copy of your workbook.

Sub RemoveRows()
  Application.ScreenUpdating = False
  With Range("A1:C" & Range("A" & Rows.Count).End(xlUp).Row)
    Range("J2").Formula = Replace("=COUNTIFS(A$2:A$#,A2,B$2:B$#,""SEA"",C$2:C$#,""C"")", "#", .Rows.Count)
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("J1:J2"), Unique:=False
    .Offset(1).EntireRow.Delete
    If .Parent.FilterMode Then .Parent.ShowAllData
    Range("J2").ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

If you happen to have a very large amount of data and both the suggested methods are too slow, post back with details as there would be a faster way (but longer code to write)

 

[Sathisc]

Thank you so much Joe and Peter. It works perfect...

 

[Peter_SSs]

Thank you so much Joe and Peter. It works perfect...

Glad that you got something that worked for you.