Hi, I'm putting together a macro that will allow a user to select two
separate workbooks and compare one column in each workbook to find any
matches across them. Where there is a match it will copy the row to a
third sheet and delete it from the original sheet, before moving on to
the next value.
I have an input box asking the user which column number should be
searched but I don't know how to make the macro use their response in
the logic test here - "If x.Value = Sheets(1).Cells(iCtr, 1).Value
Then". Could anyone give me some pointers on that please?
Dim iListCount As Integer
Dim iCtr As Integer
Dim LoopRange As Range
Dim HoldRng As Range
Dim MatchRow As Range
Dim SourceColumn As Integer
Dim SourceSheet As Range
Dim TargetSheet As Range
MsgBox ("Select Source sheet against which data will be checked")
FileName = Application.GetOpenFilename
Workbooks.Open FileName
If FileName = "" Then End
MsgBox ("Select Target sheet from which duplicates will be deleted")
Set FileName = SourceSheet
FileName2 = Application.GetOpenFilename
Workbooks.Open FileName2
If FileName2 = "" Then End
Set FileName2 = TargetSheet
FileNum = FreeFile()
' Get count of records to search through (list that will be deleted).
iListCount = Sheets(1).UsedRange.Rows.Count
SourceColumn = InputBox("Enter the number of the matching column from
Left To Right, e.g 1 for A, 2 for B", "Column Selection")
Application.ScreenUpdating = False
' Loop through the "master" list.
For Each x In Sheets(1).UsedRange
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets(1).Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets(1).Cells(iCtr, 1).EntireRow.Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "All duplicates removed"
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
If you find any spam message in the group, please send an email to:
Ayush Jain @ [email protected] or
Ashish Jain @ [email protected]
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
