使用ActiveSheet.UsedRange出现类型不匹配错误的原因分析

类型不匹配错误的核心原因是ActiveSheet.UsedRange里包含错误值单元格（比如#N/A、#VALUE!、#DIV/0!这类）。当代码执行cell.Value = "X123"时，如果cell是错误值，错误值类型无法和字符串"X123"直接比较，就会抛出类型不匹配的错误。而你直接指定的搜索范围里没有这类错误单元格，所以能正常运行。

另外还有个潜在问题：如果UsedRange里完全找不到"X123"，MySel会保持为Nothing，执行MySel.EntireColumn.Hidden = True时也会报错。

Sub HideColumn()
    Dim MySel As Range
    Dim cell As Range
    
    For Each cell In ActiveSheet.UsedRange
        ' 先排除错误值单元格，再比较内容
        If Not IsError(cell.Value) Then
            If cell.Value = "X123" Then
                If MySel Is Nothing Then
                    Set MySel = cell.EntireColumn ' 直接存整列，减少Union操作
                Else
                    Set MySel = Union(MySel, cell.EntireColumn)
                End If
            End If
        End If
    Next cell
    
    ' 判断MySel不为空再执行隐藏
    If Not MySel Is Nothing Then
        MySel.Hidden = True
    End If
End Sub

• 直接操作整列代替单个单元格，减少Union的次数，提升运行效率
• 可以用Find/FindNext方法批量定位包含"X123"的单元格，比遍历所有单元格更快，适合数据量大的场景：

Sub HideColumn_Fast()
    Dim MySel As Range
    Dim foundCell As Range
    Dim firstAddr As String
    
    With ActiveSheet.UsedRange
        Set foundCell = .Find(What:="X123", LookIn:=xlValues, LookAt:=xlWhole)
        If Not foundCell Is Nothing Then
            firstAddr = foundCell.Address
            Do
                If MySel Is Nothing Then
                    Set MySel = foundCell.EntireColumn
                Else
                    Set MySel = Union(MySel, foundCell.EntireColumn)
                End If
                Set foundCell = .FindNext(foundCell)
            Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddr
        End If
    End With
    
    If Not MySel Is Nothing Then
        MySel.Hidden = True
    End If
End Sub

内容的提问来源于stack exchange，提问作者John Keyberg