Excel宏实现排除指定文本的单元格复制

原代码功能为将工作表ALL中C、D列（从第2行开始）的非空、无错误单元格复制到A列起始区域。现在需要新增逻辑：跳过所有内容包含“no”的单元格（若需不区分大小写可调整，默认区分），仅保留其余非空无错误的单元格内容。

修改后的完整代码 #

Function RefRangeFind( _
    ByVal firstRowRange As Range, _
    Optional ByVal DisplayMessage As Boolean = False) _
As Range
    With firstRowRange.Areas(1).Rows(1)
        Dim cell As Range
        With .Resize(.Worksheet.Rows.Count - .Row + 1)
            Set cell = .Find("*", , xlFormulas, , xlByRows, xlPrevious)
            If cell Is Nothing Then
                If DisplayMessage Then
                    MsgBox "No data found in range """ & .Address(0, 0) _
                        & """ of worksheet """ & .Worksheet.Name & """!", _
                        vbExclamation
                End If
                Exit Function
            End If
        End With
        Set RefRangeFind = .Resize(cell.Row - .Row + 1)
    End With
End Function

Function IsValidCell(Value As Variant) As Boolean
    ' 判断条件：无错误、非空、内容不包含"no"
    If Not IsError(Value) Then
        If Len(Value) > 0 Then
            If InStr(1, Value, "no", vbBinaryCompare) = 0 Then ' vbBinaryCompare区分大小写，vbTextCompare不区分
                IsValidCell = True
            End If
        End If
    End If
End Function

Sub lain()
    With ThisWorkbook.Sheets("ALL")
        Dim rg As Range
        Set rg = RefRangeFind(.Range("C2:D2"))
        If rg Is Nothing Then Exit Sub
        
        Dim Data() As Variant
        Data = rg.Value
        Dim r As Long, c As Long
        
        For r = 1 To UBound(Data, 1)
            For c = 1 To UBound(Data, 2)
                ' 调用修改后的判断函数，不符合条件则设为空
                If Not IsValidCell(Data(r, c)) Then Data(r, c) = Empty
            Next c
        Next r
        
        .Range("A2").Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data
    End With
End Sub

关键修改说明 #

1. 重命名并优化判断函数：将原IsNoErrorNoBlank函数改为IsValidCell，新增InStr判断逻辑，检查单元格内容是否包含“no”。
   • 若需要不区分大小写跳过“NO”“No”等变体，可将vbBinaryCompare替换为vbTextCompare。
2. 同步更新遍历逻辑：在lain子过程的循环中，调用修改后的IsValidCell函数，将不符合条件（含“no”、空值、错误值）的单元格内容设为空，最终写入目标区域。

内容的提问来源于stack exchange，提问作者little turtle