Hiding colums

[EXCELGPS]

Hi,
I have a spreadsheet that has columns with headings (F-AA), I want to hide all the columns where the heading doesn't match whats in cell D28.

Easy way of doing this please?

 

[JackDanIce]

Re: Hidng colums

Try:

Sub HideCols()

    Dim rng As Range
    
    On Error Resume Next
    Set rng = Cells(1, 6).Resize(, 22).find(what:=Cells(28, 4).Value, LookIn:=xlValues, lookat:=xlWhole)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        For x = 6 To 27
            If Cells(1, x).Value = Cells(28, 4).Value Then Set rng = Union(rng, Cells(1, x))
        Next x
    Else
        MsgBox "Value: " & Cells(28, 4).Value & vbCrLf & vbCrLf & "Cannot be found!", vbExclamation, "Header Value Not Found"
    End If
    
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        rng.EntireColumn.Hidden = True
        Application.ScreenUpdating = True
        Set rng = Nothing
    End If
            
End Sub

 

[EXCELGPS]

Re: Hidng colums

Try:

Sub HideCols()

    Dim rng As Range
    
    On Error Resume Next
    Set rng = Cells(1, 6).Resize(, 22).find(what:=Cells(28, 4).Value, LookIn:=xlValues, lookat:=xlWhole)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        For x = 6 To 27
            If Cells(1, x).Value = Cells(28, 4).Value Then Set rng = Union(rng, Cells(1, x))
        Next x
    Else
        MsgBox "Value: " & Cells(28, 4).Value & vbCrLf & vbCrLf & "Cannot be found!", vbExclamation, "Header Value Not Found"
    End If
    
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        rng.EntireColumn.Hidden = True
        Application.ScreenUpdating = True
        Set rng = Nothing
    End If
            
End Sub

Hi thanks for reply, it's coming up with message box saying header can't be found. The data in cell 28 is a drop down list, not sure if this is causing the issue?

 

[JackDanIce]

Re: Hidng colums

What row are your headers on? The code assumes row 1

 

[EXCELGPS]

Re: Hidng colums

What row are your headers on? The code assumes row 1

Hi
They were on row 4 but changed it to row 1 and it is working now apart from it's working backwards, it's hiding the column that matches D28 where it should hide all columns not matching D28?

Thanks

 

[JackDanIce]

Re: Hidng colums

Try:

Sub HideCols()

    Dim strMsg          As String
    Dim rng             As Range
    Const HeaderRow     As Long = 1
    
    On Error Resume Next
    Set rng = Cells(HeaderRow, 6).Resize(, 22).find(what:=Cells(28, 4).Value, LookIn:=xlValues, lookat:=xlWhole)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        For x = 6 To 27
            If Cells(HeaderRow, x).Value <> Cells(28, 4).Value Then Set rng = Union(rng, Cells(HeaderRow, x))
        Next x
        Application.ScreenUpdating = False
        rng.EntireColumn.Hidden = True
        Application.ScreenUpdating = True
        Set rng = Nothing
    Else
        strMsg = "Value :@D28@1@1Cannot be found!"
        strMsg = Replace(strMsg, "@D28", Cells(28, 4).Value)
        strMsg = Replace(strMsg, "@1", vbCrLf)
        MsgBox strMsg, vbExclamation, "Header Value Not Found"
    End If
            
End Sub

Change HeaderRow to suit, code set to text for values not equal to D28

(No need to re-quote what's in immediate thread above either, able to read screen!)

 

[EXCELGPS]

Re: Hidng colums

That now hides all rows, then comes up with message box saying header can't be found.

 

[JackDanIce]

Re: Hidng colums

Did you adjust the code for the header row? The code above has

Const HeaderRow     As Long = 1

Otherwise can you share a screen shot of your sheet?

 

[EXCELGPS]

Re: Hidng colums

Yes I put that in, how do you attach a screen shot I can't see how?

Thanks

 

[JackDanIce]

Re: Hidng colums

To include a screenshot, see reply #5 in this thread by @Peter_SSs: https://www.mrexcel.com/forum/about-board/431415-how-post-screen-shot-image-forum.html Excel Jeanie

Before you do that, the code in #6 , can you test twice it by switching part in blue from <> to = and = to <> and reply with the results. They should be "opposite" of each other, i.e. columns you want hidden where header does not match D28 and then columns you do not want hidden, where header does match D28

You are running the code from the same raw data starting point and not after the macro has been run once or more?

Sub HideCols()

    Dim strMsg          As String
    Dim rng             As Range
    Const HeaderRow     As Long = 1
    
    On Error Resume Next
    Set rng = Cells(HeaderRow, 6).Resize(, 22).find(what:=Cells(28, 4).Value, LookIn:=xlValues, lookat:=xlWhole)
    On Error GoTo 0
    
    If Not rng Is Nothing Then
        For x = 6 To 27
            If Cells(HeaderRow, x).Value <> Cells(28, 4).Value Then Set rng = Union(rng, Cells(HeaderRow, x))
        Next x
        Application.ScreenUpdating = False
        rng.EntireColumn.Hidden = True
        Application.ScreenUpdating = True
        Set rng = Nothing
    Else
        strMsg = "Value :@D28@1@1Cannot be found!"
        strMsg = Replace(strMsg, "@D28", Cells(28, 4).Value)
        strMsg = Replace(strMsg, "@1", vbCrLf)
        MsgBox strMsg, vbExclamation, "Header Value Not Found"
    End If
            
End Sub