How to delete both rows if cell value equal in 2 diferent collums

[Cesar87]

Dear all, I have tried to search on other forums but can't seem to find an answer. Please help for <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>.

I have some data column A. However, the amount of data varies everytime. In addition, blanks cells may be present in column A as well.

I have some data column B. The amount of data also varies everytime but it's always equal to collum A. In addition, blank cells maybe present as well.

what I need here is to match the values in column A to column B, and if it matches I need to delete the entire row of both collums.

how do I do that in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>? A lot of forums talk about a specific value but none on non specific values. Please help.

Thank you in advance!

 

[hiker95]

Cesar87,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Sample raw data (the same color cells represent matches):

Excel 2007
	A	B
1	Title A	Title B
2	2
3	3	1
4		6
5	5
6	6
7	7	5
8
9	9	9
10	10	10
11	11	22
12	13
13
14		13
15
Sheet1

After the macro:

Excel 2007
	A	B
1	Title A	Title B
2	2
3	3	1
4
5	11	22
6
7
8
9
10
11
12
13
14
15
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Cesar87()
' hiker95, 11/05/2013
' http://www.mrexcel.com/forum/excel-questions/737298-how-delete-both-rows-if-cell-value-equal-2-diferent-collums.html
Dim r As Long, lr As Long, fr As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
For r = lr To 2 Step -1
  If Cells(r, 1) <> "" Then
    fr = 0
    On Error Resume Next
    fr = Application.Match(Cells(r, 1), Columns(2), 0)
    On Error GoTo 0
    If fr = r Then
      Rows(r).Delete
    ElseIf fr > 0 Then
      If r > fr Then
        Rows(r).Delete
        Rows(fr).Delete
      ElseIf fr > r Then
        Rows(fr).Delete
        Rows(r).Delete
      End If
    End If
  End If
Next r
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Cesar87 macro.

 

[Cesar87]

Cesar87,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

Sample raw data (the same color cells represent matches):

Excel 2007

	A	B
1	Title A	Title B
2	2
3	3	1
4		6
5	5
6	6
7	7	5
8
9	9	9
10	10	10
11	11	22
12	13
13
14		13
15

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro:

Excel 2007

	A	B
1	Title A	Title B
2	2
3	3	1
4
5	11	22
6
7
8
9
10
11
12
13
14
15

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Cesar87()
' hiker95, 11/05/2013
' http://www.mrexcel.com/forum/excel-questions/737298-how-delete-both-rows-if-cell-value-equal-2-diferent-collums.html
Dim r As Long, lr As Long, fr As Long
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
For r = lr To 2 Step -1
  If Cells(r, 1) <> "" Then
    fr = 0
    On Error Resume Next
    fr = Application.Match(Cells(r, 1), Columns(2), 0)
    On Error GoTo 0
    If fr = r Then
      Rows(r).Delete
    ElseIf fr > 0 Then
      If r > fr Then
        Rows(r).Delete
        Rows(fr).Delete
      ElseIf fr > r Then
        Rows(fr).Delete
        Rows(r).Delete
      End If
    End If
  End If
Next r
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Cesar87 macro.

Thank you a lot!!!!! That's great, it worked perfectly!!! Do you mind explaining to me what the code does stating that variable lr?

 

[hiker95]

Cesar87,

You are very welcome. Glad I could help.

Thanks for the feedback.

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.

Do you mind explaining to me what the code does stating that variable lr?

Be back in a little while.

 

[hiker95]

Cesar87,

Do you mind explaining to me what the code does stating that variable lr?

See the BOLD descriptions below:

' This tells VBA that I want to declare all my variables.
'   This is extremely important if another programmer is trying to
'   figure out what your code is doing
Option Explicit


'   Cesar87 is the name of the macro
Sub Cesar87()
' hiker95, 11/05/2013
' http://www.mrexcel.com/forum/excel-questions/737298-how-delete-both-rows-if-cell-value-equal-2-diferent-collums.html



' The following are my variables
'   r stands for row
'   lr is for the last used row in the worksheet
'   fr is for the found row when we search column B
Dim r As Long, lr As Long, fr As Long



' the following will find the last used row in the entire worksheet
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row



' When we insert or delete rows we usually start are the bottom of the raw data
'               and, we want to move up one row at a time
For r = lr To 2 Step -1



  ' If the cell in column A NOT blank
  If Cells(r, 1) <> "" Then
  

  
    '  set the fr, foundrow, variable to zero
    fr = 0
    

    
    '  If there is an error with the Application.Match calculation
    '    then continue
    On Error Resume Next
    

    
    '  find the matching row of the cell in column A, in column B = 2
    fr = Application.Match(Cells(r, 1), Columns(2), 0)
    

    
    '  this will reset the error in order to continue
    On Error GoTo 0
    

    
    '  If the fr, foundrow, is = the the row in column A
    '    if A7 = B7
    If fr = r Then
    

    
      '  delete the row
      Rows(r).Delete
      

      
    '  If the fr, foundrow, is greater than zero, continue
    ElseIf fr > 0 Then
    

    
      '  If the current row, looping row, is greater that the fr, foundrow      If r > fr Then

      
      
        '  delete the row we are search from, for column A
        Rows(r).Delete
        

        
        '  delete the fr, foundrow, for column B
        Rows(fr).Delete
        

        
      '  If the fr, foundrow is greater that the search row
      ElseIf fr > r Then
      

      
        '  delete the fr, foundrow in column B first
        Rows(fr).Delete
        

        
        '  delete the r, the row we are searching from
        Rows(r).Delete
        


      End If
    End If
  End If
  

  
'  loop up in column A, one row, and, and do more searching/looping
Next r
End Sub

 

[Cesar87]

Thank you so much Hiker95!!!!!!!! And Thanks for the tip too have a great day!!!