Delete row with same ID but if found B2B-111 in column C of that ID.

[parscon]

I need a VBA delete the row if cloumn A is same and also find B2B-111 in column C but it will remove all row that are same with column A.

For example :

Column A Column B ColumnC
32843 11410505 PAR
32855 11109962 B2B-111
32855 11109962 PAR

Result after run VBA :

Column A Column B ColumnC
32843 11410505 PAR


Thank you for your time and help

[mperrah]

You can try this.
I added some extra rows of similar data on a test sheet
and this code seems do do what you asked if I understand your request.



Sub vba52262()
Dim x, r As Long
Dim lr, mchRng As Range

lr = Cells(Rows.Count, "A").End(xlUp).Row

For x = 1 To lr
If Cells(x, 1).Value = Cells(x + 1, 1) And _
Cells(x, 3).Value = "B2B-111" Then
Set mchRng = Range("A" & x)
mchRng.Resize(2).EntireRow.Delete shift:=xlUp
End If
Next x

End Sub
hope this helps
-mark

[parscon]

Dear mperrah
Thanks for your fast reply , please check the sample file and also check the result sheet , when run the code must give the data on sheet result.13140

[mperrah]

So instead of delete the row you want it moved to results sheet?
1)are you wanting both rows moved
or
2) all rows with matching cell values in Column A ?

[parscon]

No , Just i want to show the result , it will be remove , Yes ,first check the column A if they are same and find B2B-111 in same information on column A will delete all of them .

[mperrah]

Sorry I'm still not clear.
1) the rows not deleted get moved or copied to results sheet
2) a list of deleted rows get copied to results

make a workbook with 3 sheets
sheet1 is raw data (before macro)
sheet2 is what the sheets1 data looks like after macro
sheet3 what you want on results sheet after macro

[parscon]

No , All will be happen in Sheet1, just i want to show you result and create result sheet ,that mean the items that in result will be in sheet1 after run macro.

[mperrah]

I'm sorry but im still not clear as to what you want on the result sheet. is it a copy of sheet1 after the macro?
could you manually copy the values as you want the results to look for at least 4 matched values from sheet1

[parscon]

I have these data on Sheet 1



Column A Column B Column C
1 DATA1
1 DATA2 B2B-111
1 DATA3 PAB1
2 DATA4
2 DATA5
2 DATA6
3 DATA7
3 DATA8 B2B-111
3 DATA9



When Run VBA this will be our result :



Column A Column B Column C
2 DATA4
2 DATA5
2 DATA6

[mperrah]

Our result means contents of sheets("Results") ?
And what do you want on sheets("sheet1") ?

Column A Column B Column C DATA1
1 DATA2 B2B-111
1 DATA3

3 DATA7
3 DATA8 B2B-111
3 DATA9

[mperrah]

This code looks for "B2B-111" in column C searching from bottom up (good to delete rows bottom up)
when a match is found it marks that as the starting row for deletion,
then it scans back down the rows for the value in Column a for matching values and adds up the matches,
then marks the last match as the end of rows to be deleted, then deletes that group of rows
as far as what you want on the "Results" sheet I'm still not clear what you are asking.

Sub vba52262()

Dim x, r, i, m As Long
Dim lr, stMch, endMch, grpMch As Range

lr = Cells(Rows.Count, "A").End(xlUp).Row
For x = lr To 1 Step -1
If Cells(x, 3).Value = "B2B-111" Then
m = 0
Set stMch = Cells(x, 1)
For r = x To x + 10
If stMch.Value = Cells(r, 1).Value Then
m = m + 1
Else
End If
Next r
Set endMch = Range("A" & x + m - 1)
Set grpMch = Range(stMch, endMch)
grpMch.EntireRow.Delete shift:=xlUp
End If
Next x
End Sub