根据工作簿1中的单元格值删除工作簿2中的多行

Question

1. 我需要VBA代码，它将根据工作簿1（A1）中单元格的值打开工作簿2
2. 在B:B列（Workbook1）中存在值，如果这些值与A:A列（Workbook2）中可用的值相同，则删除（Workbook2）中包含相同值的整行

有人能帮我创建代码吗？

我试过这个。。。。。

    Private Sub CommandButton1_Click()
Dim WB1, WB2 As Workbook
Dim WS1, WS2 As Worksheet
Set WB1 = ThisWorkbook
CSN = Cells(1, 1)
Set WB2 = Workbooks.Open("C:UsersBaselDesktop" & CSN & "")
Set WS1 = WB1.Worksheets("sheet1")
Set WS2 = WB2.Worksheets("Sheet1")
LastRow1 = WB1.WS1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
LastRow2 = WB2.WS2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To 20
If WB1.WS1.Cells(i, 2).Value = WB2.WS2.Cells(i, 1).Value Then
WB2.WS2.Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub

Answer 1

假设如下：

1） 您已经创建了两个名为WorkBook1和WorkBook2 的工作簿对象

2） 你正在将两本书中的"A"栏与名为"表1"的表进行比较

Dim WB1sheet As Worksheet
Dim WB2sheet As Worksheet
Dim cell As Range
Dim cell2 As Range
Set WB1sheet = WorkBook1.Sheets("sheet1")
Set wb2sheet = Workbook2.Sheets("sheet1")
'Loop through colum A
For Each cell In WB1sheet.Range("a1", "a1000000")
   ' for each loop through the other sheet
   If cell = "" Then
      Exit For
   End If
   For Each cell2 In wb2sheet.Range("a1", "a1000000")
      If cell = cell2 Then
         cell2.ClearContents
         Exit For
      End If
   Next cell2
Next cell
End Sub

这只会留下一个空白单元格，而不会删除行，删除行更为棘手，因为for each循环将脱离汇点并错过行。如果您需要删除行而不仅仅是清除行，那么简单的解决方法是在之后对列进行排序，然后空格将全部移到底部。只需使用宏记录记录排序即可。

祝好运

Answer 2

真的很感谢我对代码做了一些修改，但它确实有效。

    Private Sub CommandButton1_Click()
Dim WB1, WB2 As Workbook
Dim WS1, WS2 As Worksheet
Dim CELL1 As Range
Dim CELL2 As Range
CSN = Cells(1, 1)
Set WB1 = ThisWorkbook
Set WB2 = Workbooks.Open("C:UsersBaselDesktop" & CSN & "")
Set WS1 = WB1.Worksheets("sheet1")
Set WS2 = WB2.Worksheets("Sheet1")
For Each CELL1 In WS1.Range("B1", "B10")
If CELL1 = "" Then
Exit For
End If
For Each CELL2 In WS2.Range("A1", "A10")
If CELL1 = CELL2 Then
CELL2.EntireRow.Delete
Exit For
End If
Next CELL2
Next CELL1
WB2.Save
WB2.Close
Range("B:B").ClearContents
End Sub`

`