excel - 一个单元格中的串联数据；当 1 条信息不存在时删除整行

Question

我运行了一份报告，其中显示了一份员工列表，其中包含与他们的就业记录相关的所有信息。其中一件事是他们所有员工角色的串联列表，以及有效的截止日期和开始日期。例如，如果可能的角色列表如下:

角色 1

角色 1 – 培训师

角色 2

角色 2 – 培训师

角色 3

角色 3 – 培训师

见习员工

前员工

试用员工

还有一个员工被勾选为角色 1，角色 1 – 培训师，角色 2，他们曾经是角色 3，信息将显示在一个单元格中，如下所示:

角色 1 – 2020 年 1 月 1 日至不适用；角色 1 – 培训师 – 2020 年 1 月 1 日；角色 2 –
2020 年 1 月 1 日至 N/A；角色 3 – 2020 年 1 月 1 日至 2020 年 2 月 28 日

对于我正在使用的报告，我只希望能够看到角色 1 中的活跃员工列表(因此，不包括从角色 1 退休但仍活跃于其他角色的人)。

我有以下代码允许我从列表中删除任何在他们的记录中没有角色 1 的人:

Sub deleteifnotR1 ()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Employee Record")

    lastRow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("E1:E" & lastRow)

    'filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*Role 1*"
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        If lr > 1 Then
         Range("D2:D" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
    End With

    ' turn off the filters
    ws.AutoFilterMode = False

End Sub

这样做的问题是，它仍然会给我留下一份前雇员名单，以及可能已经从角色 1 退休但仍然是角色 3 的在职员工等。

在考虑如何解决这个问题时，我有两个想法——首先，是否可以取消连接这些数据？例如，要在顶部插入具有所有不同可能角色的新列，然后有一个宏来识别某人何时在他们的记录中具有“角色 1 - 培训师”并将该数据移动到正确的列？然后我可以使用上面代码的变体来删除角色 1 列中没有“N/A”的任何人。

或者，是否可以修改以下行:

.AutoFilter Field:=1, Criteria1:="<>*Role 1*"

以某种方式搜索“角色 1 – ANY DATE to N/A”？这将过滤掉前雇员和已从角色 1 退休但仅此而已的雇员。

(理想情况下，我只是修改报告而不是事后修复它，但我无权访问我们系统的那部分，而且这样做的人也不愿意对其进行任何开发)。

在此先感谢您的帮助。

Answer 1

不幸的是，你不能在 AutoFilter 中使用更多的内部通配符。 .但也许可以尝试使用 Like运算符(operator)或者更灵活；常用表达。

尝试类似:

Sub deleteifnotR1()
Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long, x As Long

    Set ws = ActiveWorkbook.Sheets("Employee Record")
    lastRow = ws.Range("E" & ws.Rows.Count).End(xlUp).Row

    With CreateObject("VBScript.RegExp")
        .Pattern = "\b[rR]ole 1 - \d{1,2}\/\d{1,2}\/\d{4} to N\/A\b"
        .Global = True
        For x = lastRow To 2 Step -1
            If Not .Test(ws.Cells(x, 5).Value) Then
                ws.Cells(x, 5).EntireRow.Delete
            End If
        Next x
    End With

Application.ScreenUpdating = True
End Sub

使用的模式更好地可视化，例如:



如您所见，它允许“角色 1”和“角色 1”，并且确实允许写成“2020 年 1 月 1 日”或“2020 年 10 月 10 日”这样的日期。

如果您有大量数据，更好/更快的方法是通过数组在内存中执行此操作。希望这能让你开始。

Note: You have currently used dash instead of a hyphen after "Role 1 ".