67721

VBA remove matching first & last names across 2 worksheets

Question:

I need help modifying this code to match First and Last names across 2 worksheets, then remove matches from the Sub sheet. At the moment it only matches 2 columns across 1 sheet. Specifics:

How do i change this code so Names on 'Sheet 1' Column 'B' are Matched to names on 'sheet 2' column 'E' & all matches are deleted from 'Sheet 1". Same is repeated for 'Sheet 1' Column 'C' to 'Sheet 2' Column 'F'.

Sub CompareNames() Dim rngDel As Range Dim rngFound As Range Dim varWord As Variant Dim strFirst As String With Sheets("ADULT Sign On Sheet") For Each varWord In Application.Transpose(.Range("A1", .Cells(.Rows.Count,"A").End(xlUp)).Value) If Len(varWord) > 0 Then Set rngFound = .Columns("B").Find(varWord, .Cells(.Rows.Count, "B"), xlValues, xlPart) If Not rngFound Is Nothing Then strFirst = rngFound.Address Do If Not rngDel Is Nothing Then Set rngDel = Union(rngDel, rngFound) Else Set rngDel = rngFound Set rngFound = .Columns("B").Find(varWord, rngFound, xlValues, xlPart) Loop While rngFound.Address <> strFirst End If End If Next varWord End With If Not rngDel Is Nothing Then rngDel.Delete Set rngDel = Nothing Set rngFound = Nothing End Sub

Answer1:

Loops through all values in Sheet1 Column B. If that value is found in Sheet2 Column E, the entire row in Sheet1 is deleted. Then it loops through all values in Sheet1 Column C. If that value is found in Sheet2 Column F, the entire row in Sheet1 is deleted.

Sub DeleteCopy() Dim LastRow As Long Dim CurRow As Long Dim DestLast As Long LastRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row DestLast = Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Row For CurRow = 2 To LastRow 'Assumes your first row of data is in row 2 If Not Sheets("Sheet2").Range("E2:E" & DestLast).Find(Sheets("Sheet1").Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then Sheets("Sheet1").Range("B" & CurRow).Value = "" Else End If Next CurRow LastRow = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row DestLast = Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp).Row For CurRow = 2 To LastRow 'Assumes your first row of data is in row 2 If Not Sheets("Sheet2").Range("F2:F" & DestLast).Find(Sheets("Sheet1").Range("C" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then Sheets("Sheet1").Range("C" & CurRow).Value = "" Else End If Next CurRow End Sub

Answer2:

Try this, you will have to call it twice once with the first criteria and then again with the second critiera

I think I have it set up properly for the first criteria

Sub DeleteIfMatchFound() Dim SearchValues As Variant Dim wsSource As Worksheet, wsTarget As Worksheet Dim sLR As Long, tLR As Long, i As Long Set wsSource = ThisWorkbook.Worksheets("Sheet1") Set wsTarget = ThisWorkbook.Worksheets("Sheet2") sLR = wsSource.Range("B" & wsSource.Rows.Count).End(xlUp).Row tLR = wsTarget.Range("E" & wsSource.Rows.Count).End(xlUp).Row SearchValues = wsSource.Range("B2:B" & sLR).Value For i = 1 To (tLR - 1) If Not IsError(Application.match(SearchValues(i, 1), wsTarget.Range("E2:E" & tLR), 0)) Then wsTarget.Rows(i + 1).Delete End If Next i End Sub

Recommend

  • Old visual basic mid and left functions
  • using vlookup to find matching values in a column
  • How to optimize a VBA Macro that adds a link to multiple Arabic words at once
  • how to convert multiple txt files and saving them to excel
  • Excel find & replace cell contents based on contents of a list in another sheet [duplicate]
  • Replacing text in same column for all sheets
  • Response 400 from Google Vision API OCR with a base64 string of specified image
  • Access 2013 VBA: Query recipient email address
  • Access VBA: how to dump OLE field content into bytes
  • Excel VBA - Looking up a string with wildcards
  • Accessing Lotus Notes database from Excel VBA - how do I pick up COLUMNVALUES?
  • Multiplying arrays with scalars and adding in VBA
  • Matlab code optimization and removing loops [closed]
  • Multiply two data frames with similar index in python pandas
  • Multiprocessing in Python: how to implement a loop over “apply_async” as “map_async” using a callbac
  • How to calculate total Fridays between two dates in excel on weekly, biweekly and monthly basis?
  • Excel passing a range into a function
  • Excel distinct count with conditions
  • xtable - background colour of added rows
  • cell spacing in div table
  • several dataProvider per one Test in TestNG
  • Using Sax parsing to edit and write XML in VB6
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Excel's Macro-Recorder usage
  • Django: Count of Group Elements
  • Spring Data JPA custom method causing PropertyReferenceException
  • Retrieving value from sql ExecuteScalar()
  • C# - Serializing and deserializing static member
  • Excel - Autoshape get it's name from cell (value)
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • How to handle AllServersUnavailable Exception
  • vba code to select only visible cells in specific column except heading
  • VBA Convert delimiter text file to Excel
  • Do I've to free mysql result after storing it?
  • Transpose CSV data with awk (pivot transformation)
  • How to include full .NET prerequisite for Wix Burn installer
  • Is it possible to post an object from jquery to bottle.py?
  • Sorting a 2D array using the second column C++
  • Reading document lines to the user (python)