Code below is supposed to hide all columns where any of its cells contain a certain value. If I directly specify a search Range, it works. However, if I use "ActiveSheet.UsedRange", it throws a type mismatch error. What is going on?

Sub HideColumn()

    Dim MySel As Range
    
    For Each cell In ActiveSheet.UsedRange
        If cell.Value = "X123" Then
            If MySel Is Nothing Then
                Set MySel = cell
            Else
                Set MySel = Union(MySel, cell)
            End If
        End If
    Next cell
    
    MySel.EntireColumn.Hidden = True
End Sub

CodePudding user response：

Hide Columns of Cells Equal To a String

• If a cell contains an error value, the line If cell.Value = "X123" Then will fail with a Type mismatch error. In the following code, this is handled by converting the cell value to a string with CStr(cell.Value). Another way would be to add an outer (preceding) If statement If Not IsError(cell) Then.
• Option Explicit would have warned you that the cell variable is not declared forcing you to do Dim cell As Range. Why don't you use it?

Option Explicit

Sub HideColumns()

    If ActiveSheet Is Nothing Then Exit Sub ' no visible workbooks open
    If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' not a worksheet

    Dim rg As Range: Set rg = ActiveSheet.UsedRange

    Dim crg As Range, cell As Range, urg As Range
    
    For Each crg In rg.Columns
        For Each cell In crg.Cells
            If StrComp(CStr(cell.Value), "X123", vbTextCompare) = 0 Then
                If urg Is Nothing Then
                    Set urg = cell
                Else
                    Set urg = Union(urg, cell)
                End If
                Exit For ' match in column found; no need to loop anymore
            End If
        Next cell
    Next crg
    
    rg.EntireColumn.Hidden = False ' unhide all columns
    If Not urg Is Nothing Then urg.EntireColumn.Hidden = True ' hide matching

End Sub