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>
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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?
 
Upvote 0
Hi Joe,

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

Thanks,
Sathish
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top