Excel比较两列，将匹配的行复制到新工作表

1 回答

• 0

  我没有看到你有如下数据

  将列C与B进行比较

  A       B       C
  Row 1   Value 1 Value 3
  Row 2   Value 2 Value 5
  Row 3   Value 3 Value 8
  Row 4   Value 4 Value 3
  Row 5   Value 5 Value 5
  Row 6   Value 6 Value 8
  Row 7   Value 7 Value 3
  Row 8   Value 8 Value 5
  Row 9   Value 9 Value 8
  

  你想要在sheet2中得到结果

  A       B
  Row 3   Value 3
  Row 5   Value 5
  Row 8   Value 8
  

  请尝试以下代码

  Sub Macro1()
  
      Dim Counter As Long
  
      Dim lastRow As Long
      lastRow = 10
      Dim arrColF(10) As String
  
      Sheets("Sheet1").Select
  
      For Counter = 1 To lastRow Step 1
          arrColF(Counter - 1) = Sheet1.Cells(Counter, 3).Value
      Next Counter
  
      arrStr = Join(arrColF, ", ")
      Dim strRange As String
  
      For Counter = 1 To lastRow Step 1
          If InStr(1, arrStr, Sheet1.Cells(Counter, 2).Value & ", ") > 0 Then
              strRange = strRange & "A" & Counter & ":B" & Counter
              If Trim(strRange) <> "" Then strRange = strRange & ","
          End If
      Next Counter
      strRange = Left(strRange, Len(strRange) - 1)
  
      Sheet1.Range(strRange).Select
      Selection.Copy
      Sheets("Sheet2").Select
      Range("A1").Select
      ActiveSheet.Paste
  
  End Sub
  

  回复于 2024-04-19T08:22:00+08:00