|
本帖最后由 duquancai 于 2019-9-14 15:19 编辑
jamchen1314 发表于 2019-9-14 12:49
换句话说,A列名字相同的情况下,只要对应的B列包含“完成”字符,则删除所有包含此名字的行 - Sub main()
- Dim arr, resArr(), r&, i&, j&, n&, c As Range, myKeys, myItems, temp
- Set c = Range("a:a").Find("*", , , , 1, 2)
- If c Is Nothing Then Exit Sub
- r = c.Row
- If r = 1 Then Exit Sub
- arr = Range("a1:b" & r)
- Dim d As Object
- Set d = CreateObject("Scripting.Dictionary")
- Call get_dic(d, arr)
- If d.Count = 0 Then Exit Sub
- ReDim resArr(0 To UBound(arr) - 1, 1 To 2)
- resArr(0, 1) = arr(1, 1): resArr(0, 2) = arr(1, 2)
- myKeys = d.keys(): myItems = d.items()
- For i = 0 To d.Count - 1
- temp = Split(myItems(i), ";")
- If UBound(temp) > 0 Then
- For j = 0 To UBound(temp)
- If Not is_complit(temp(j)) Then
- n = n + 1
- resArr(n, 1) = myKeys(i): resArr(n, 2) = temp(j)
- End If
- Next
- Else
- n = n + 1
- resArr(n, 1) = myKeys(i): resArr(n, 2) = myItems(i)
- End If
- Next
- Range("c1").Resize(n + 1, 2) = resArr '输出区域自己决定!
- End Sub
- Sub get_dic(ByRef d As Object, ByVal arr)
- Dim i&
- For i = 2 To UBound(arr)
- If Len(arr(i, 1)) Then
- If Not d.exists(arr(i, 1)) Then
- d(arr(i, 1)) = arr(i, 2)
- Else
- d(arr(i, 1)) = d(arr(i, 1)) + ";" + arr(i, 2)
- End If
- End If
- Next
- End Sub
- Function is_complit(ByVal s As String) As Boolean
- Dim re As Object
- Set re = CreateObject("VBScript.Regexp")
- re.Pattern = "日完成"
- is_complit = re.test(s)
- End Function
复制代码
|
评分
-
1
查看全部评分
-
|