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

C

Cesar87

New Member
Joined
Nov 5, 2013
Messages
3
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!
 
Sort by date Sort by votes
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
AB
1Title ATitle B
22
331
46
55
66
775
8
999
101010
111122
1213
13
1413
15
Sheet1


After the macro:


Excel 2007
AB
1Title ATitle B
22
331
4
51122
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.

Code: 
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.
 
Upvote 0
hiker95 said:
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
AB
Title ATitle B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCD5B4, align: right"]6[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #99FF33, align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FCD5B4, align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #99FF33, align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #99FF33, align: right"]13[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #99FF33, align: right"]13[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



After the macro:

Excel 2007
AB
Title ATitle B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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.

Code: 
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.
Click to expand...

Thank you a lot!!!!! That's great, it worked perfectly!!! Do you mind explaining to me what the code does stating that variable lr?
 
Upvote 0
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?
Click to expand...

Be back in a little while.
 
Upvote 0
Cesar87,

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

See the BOLD descriptions below:


Rich (BB code): 
' 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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
N
  • Question Question
Color duplicates rows in two different sets of data and also the rows which are present in both sets of data.
Replies
0
Views
487
Noob_Excel
N
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
J
  • Solved
Macro to delete row when value of two cells is not equal
Replies
8
Views
1K
johnnyL
J
F
  • Solved
Excel how to add in my email body the value of a certain cell.
Replies
4
Views
1K
Florida1510
F

Share this page

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