Showing/Hiding Columns

[Saighead]

Hi,

I have a script that searches for a specific name in the first cell of every column and hides or shows an entire column when there's a match. It works just fine while there're no filters applied to any of the columns, in which case it does nothing. How can I fix this?

Private Sub chbxProduct_Click()


    Dim xRg As Range
    Dim xRgUni As Range
    Dim xFirstAddress As String
    Dim xProduct As String
    On Error Resume Next
    xProduct = "Product"
    
    If ActiveSheet.Name = "Products" Then
        Set xRg = Range("A1:Z1").Find(xProduct, , xlFormulas, xlWhole, , , True)
            If Not xRg Is Nothing Then
                xFirstAddress = xRg.Address
                Do
                    Set xRg = Range("A1:Z1").FindNext(xRg)
                    If xRgUni Is Nothing Then
                        Set xRgUni = xRg
                    Else
                        Set xRgUni = Application.Union(xRgUni, xRg)
                    End If
                Loop While (Not xRg Is Nothing) And (xRg.Address <> xFirstAddress)
            End If
        If chbxProduct.Value = True Then
            xRgUni.EntireColumn.Hidden = False
        Else
            xRgUni.EntireColumn.Hidden = True
        End If
    End If


End Sub

————————————
Also posted here.

 

[mumps]

Try:

Private Sub chbxProduct_Click()
    Application.ScreenUpdating = False
    Dim lCol As Long, x As Long
    lCol = Sheets("Products").Cells(1, Columns.Count).End(xlToLeft).Column
    If chbxProduct.Value = True Then
        For x = lCol To 1 Step -1
            If Cells(1, x) = "Product" Then
                Columns(x).Hidden = True
            End If
        Next x
    Else
        For x = lCol To 1 Step -1
            If Cells(1, x) = "Product" Then
                Columns(x).Hidden = False
            End If
        Next x
    End If
    Application.ScreenUpdating = True
End Sub

 

[Saighead]

@mumps

Nope, sorry. Your code does hide the matching column even with filters on, but then fails to unhide it...

 

[mumps]

I tested the macro on some dummy data and it worked properly. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Cross-posted: https://www.excelforum.com/excel-pr...77094-showing-hiding-columns.html#post5125777

 

[Saighead]

@mumps There are no other macros at play here. Maybe what's important is the version of MS Office. A guy on the other forum tells me that his Excel 2013 handles my original code just fine even with filters on, while my Excel 2010 is playing hard to get...

PS. Can't upload my actual file, sorry. Way too many things would need to be removed from it first...

 

[mumps]

I was also using Excel 2010. You wouldn't need the entire workbook, just enough data to make it representative.