Delete rows with reference

Sathisc

Board Regular
Joined
Jul 29, 2008
Messages
103
Hi,

I have a data consists of 3 column as given below

ABC
10252554SEAC
10252554LSFMC
10252554DGPMC
10252554BOOMC
10252554TSDBTO
10252554THDBTO
10252548SEAP
10252548LSFMC
10252548DGPMC
10252548BOOMC
10252548TSDBTO
10252548THDBTO

<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

ABC
10252548SEAP
10252548LSFMC
10252548DGPMC
10252548BOOMC
10252548TSDBTO
10252548THDBTO

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

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,512
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 29, 2008
Messages
103
Hi Joe,

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

Thanks,
Sathish
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,512
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 29, 2008
Messages
103

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,512
Office Version
  1. 365
Platform
  1. Windows
Try this code:
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

Board Regular
Joined
Jul 29, 2008
Messages
103

ADVERTISEMENT

Try this code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,512
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 29, 2008
Messages
103
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,512
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,476
Messages
5,529,070
Members
409,849
Latest member
J7House1984
Top