Delete rows with reference

[Sathisc]

Hi,

I have a data consists of 3 column as given below

[TABLE="width: 192"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]10252554[/TD]
[TD]SEA[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]10252554[/TD]
[TD]LSF[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252554[/TD]
[TD]DGP[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252554[/TD]
[TD]BOO[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252554[/TD]
[TD]TSD[/TD]
[TD]BTO[/TD]
[/TR]
[TR]
[TD]10252554[/TD]
[TD]THD[/TD]
[TD]BTO[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]SEA[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]LSF[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]DGP[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]BOO[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]TSD[/TD]
[TD]BTO[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]THD[/TD]
[TD]BTO[/TD]
[/TR]
</tbody><colgroup><col span="3"></colgroup>[/TABLE]


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

[TABLE="width: 192"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]SEA[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]LSF[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]DGP[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]BOO[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]TSD[/TD]
[TD]BTO[/TD]
[/TR]
[TR]
[TD]10252548[/TD]
[TD]THD[/TD]
[TD]BTO[/TD]
[/TR]
</tbody><colgroup><col span="3"></colgroup>[/TABLE]

 

[Joe4]

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

Regarding the column B and C checks, is that ANY row that has that, or just the first row?
What if SEA is in column B, and C is in column C, but on a DIFFERENT row?

 

[Sathisc]

Hi Joe,

the data ("SEA" in colum B and "C" in column C) and can be in any row.

Thanks,
Sathish

 

[Joe4]

the data ("SEA" in colum B and "C" in column C) and can be in any row.

OK, but do they need to be in the SAME row (whatever row that is)?
Like what if "SEA" is in cell B2, and "C" is in cell C9.
Does that qualify?

 

[Sathisc]

OK, but do they need to be in the SAME row (whatever row that is)?
Like what if "SEA" is in cell B2, and "C" is in cell C9.
Does that qualify?

The data has to be in the same row. delete needs to happen only if both in the same row
delete should happen only if "C" also present in cell C2

 

[Joe4]

Try this code:

Sub MyDeleteRows()

    Dim RowCt As Long
    
'   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 data
        ActiveSheet.Range("A:C").AutoFilter Field:=1, Criteria1:="10252554"
'       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
    End If
    
End Sub

 

[Sathisc]

Try this code:

Sub MyDeleteRows()

    Dim RowCt As Long
    
'   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 data
        ActiveSheet.Range("A:C").AutoFilter Field:=1, Criteria1:="10252554"
'       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
    End If
    
End Sub

Thanks Joe for you response.
however the criteria should only be "SEA" in column B and "C" in column C
this combination can come with any number in column A.
so if the combination is present, then the number present in the same row in Colum A should get deleted throughout.

 

[Joe4]

however the criteria should only be "SEA" in column B and "C" in column C
this combination can come with any number in column A.
so if the combination is present, then the number present in the same row in Colum A should get deleted throughout.

Have you tried it, or are you just looking at the code and making a guess?
I am asking because when I ran the code on the sample data you gave, it EXACTLY produced the required output you listed.
Are you saying that the required output you originally listed is not correct, and it should behave differently?

 

[Sathisc]

Have you tried it, or are you just looking at the code and making a guess?
I am asking because when I ran the code on the sample data you gave, it EXACTLY produced the required output you listed.
Are you saying that the required output you originally listed is not correct, and it should behave differently?

I have tried joe, its perfectly working for the number "10252554" but when the combination appears with some other number its not getting deleted.

You are right, may be I didn't express perfectly. If the combination appears, the number present(any number) in column A should get deleted throughout.

 

[Joe4]

So you want it to be more dynamic, and run off of any number with the "SEA"/"C" combination, and not just "10252554" specifically?
That is going to take a little more doing. I will need to think about that and come up with a good plan of attack.