如何从工作表1中删除不在工作表2中的所有行

Question

朋友我有两个Excel工作表，如下所示...

**Sheet_1**           **Sheet_2**
ID  Name  Address      ID Name Address
1    A     Any         2   B     Any
2    B     Any         4   D     Any
3    C     Any         5   E     Any
4    D     Any
5    E     Any

我想从Sheet_1中删除不在Sheet_2中的所有行。

注意：工作表的 ID 是唯一的

Answer 1

我不确定我是否正确，但您想删除不在Sheet2中的行？

所以这将使你的Sheet1成为Sheet2的副本，不是吗？

好吧，无论如何，这是主要Sub的代码：

Sub Main()
Set idsToExclude = CreateObject("Scripting.Dictionary"): idsToExclude.CompareMode = TextCompare
'fill dictionary with IDs from sheet 2
Set idsToExclude = CreateDictFromColumns("Sheet2", "A", "B")
'find last populated row
xEndRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'iterate all rows from bottom to top
For i = xEndRow To 2 Step -1
'get value of cell at current row and 1st column
currentCellValue = ActiveSheet.Cells(i, 1).Value
'if row doesnt met criteria, delete it
If Not idsToExclude.Exists(currentCellValue) Then
Rows(i).Delete
End If
Next
End Sub

以及从特定Sheet获取 ID 和名称Function：

Function CreateDictFromColumns(sheet As String, keyCol As String, valCol As String) As Object
Set dict = CreateObject("Scripting.Dictionary"): dict.CompareMode = TextCompare
Dim rng As Range
Dim i As Long
Dim lastCol As Long '// for non-adjacent ("A:ZZ")
Dim lastRow As Long
lastRow = Sheets(sheet).Range(keyCol & Sheets(sheet).Rows.Count).End(xlUp).Row
Set rng = Sheets(sheet).Range(keyCol & "1:" & valCol & lastRow)
lastCol = rng.Columns.Count
For i = 2 To lastRow
If (rng(i, 1).Value = "") Then Exit Function
dict.Add rng(i, 1).Value, rng(i, lastCol).Value
Next
Set CreateDictFromColumns = dict
End Function

注意：如果要进行相反的操作(删除Sheet1中的ID和Sheet2中的ID(，只需从以下行中删除Not运算符：

If Not idsToExclude.Exists(currentCellValue) Then

如您所见，某些部分是硬编码的。我的建议是调整这些部分并使其更具动态性，由于缺乏问题的细节，我不得不这样写。