Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

delete row if 2 cells match in excel

This is a discussion on delete row if 2 cells match in excel within the Microsoft Office support forums, part of the Tech Support Forum category. i'm trying to write an excel macro that compares a value in column A on Sheet2 to a value in


Closed Thread
 
Thread Tools Search this Thread
Old 10-23-2008, 10:35 AM   #1
Registered Member
 
Join Date: Oct 2008
Posts: 4
OS: Vista



i'm trying to write an excel macro that compares a value in column A on Sheet2 to a value in column B in sheet1. if a match, then delete the
delete entire row on sheet1.

can anyone help?

__________________
happycats is offline  
Old 10-23-2008, 06:50 PM   #2
Registered Member
 
Join Date: May 2008
Location: Baltimore, Maryland
Posts: 160
OS: Windows XP SP3



The following example assumes that row 1 on each sheet contains headers, and that the actual data starts with row 2.

Code:
Sub remove()
    Dim sr1 As Long
    Dim sr2 As Long
    For sr1 = Sheets("Sheet1").Range("B2").CurrentRegion.Rows.Count To 2 Step -1
        For sr2 = 1 To Sheets("Sheet2").Range("A2").CurrentRegion.Rows.Count
            If Sheets("Sheet1").Cells(sr1, 2).Value = Sheets("Sheet2").Cells(sr2, 1).Value Then
                Sheets("Sheet1").Rows(sr1).EntireRow.Delete
                Exit For
            End If
        Next sr2
    Next sr1
End Sub
The code goes through each value in column B on Sheet1 and looks for a match in column A on Sheet2. If the value is found on Sheet2, then the row on Sheet1 containing that value is deleted. Modify as necessary.

__________________
David M58 is offline  
Old 02-25-2009, 12:36 PM   #3
Registered Member
 
Join Date: Feb 2009
Posts: 1
OS: win xp



This macro would be perfect for me if instead of deleting the row, I add the numbers in the row. So I want to match A2 on one sheet to column A in another sheet (the person may be listed multiple times so I want this to add all of the rows where that persons name matches. Make sense?

Now, what if I don't want it to do the whole row...but just 7 cells in the row? So, if A2 matches any cells in A of another sheet, it'll add columns B-H for those rows.

Is this difficult or what? Andrea
__________________
ancroley is offline  
Old 02-26-2009, 08:08 PM   #4
Registered Member
 
Join Date: May 2008
Location: Baltimore, Maryland
Posts: 160
OS: Windows XP SP3



Andrea, take a look at the following code:

Code:
Sub rowsum()
    Dim sr1 As Long
    Dim sr2 As Long
    For sr1 = Sheets("Sheet1").Range("A2").CurrentRegion.Rows.Count To 2 Step -1
        For sr2 = 1 To Sheets("Sheet2").Range("A2").CurrentRegion.Rows.Count
            If Sheets("Sheet1").Cells(sr1, 1).Value = Sheets("Sheet2").Cells(sr2, 1).Value Then
                Sheets("Sheet1").Cells(sr1, 9).Formula = "=SUM(B" & sr1 & ":H" & sr1 & ")"
                Exit For
            End If
        Next sr2
    Next sr1
End Sub
The code goes through each value in column A on Sheet1 and looks for a match in column A on Sheet2. If the value is found on Sheet2, the code will insert a formula in column I in the same row as the matched value on Sheet1. The formula will sum the values in columns B-H. Modify as necessary.
__________________
David M58 is offline  
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 11:39 AM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts