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?

 

[EXCELGPS]

Re: Hidng colums

With = it hides the column that matches D28
With <> it hides all columns

 

[JackDanIce]

Re: Hidng colums

Try:

Sub HideCols()
    
    Dim x               As Long
    Dim rng             As Range
    Const HeaderRow     As Long = 1
    
    Application.ScreenUpdating = False

    Cells(HeaderRow, 6).Resize(, 22).EntireColumn.Hidden = False

    For x = 6 To 27
        If Cells(HeaderRow, x).Value <> Cells(28, 4).Value Then
            If rng Is Nothing Then
                Set rng = Cells(HeaderRow, x)
            Else
                Set rng = Union(rng, Cells(HeaderRow, x))
            End If
        End If
    Next x
        
    rng.EntireColumn.Hidden = True
    
    Application.ScreenUpdating = True
    Set rng = Nothing
        
End Sub

 

[EXCELGPS]

Re: Hidng colums

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

No, when you change the value in D28 from the drop down list the columns need to switch and re-hide to match the new value in D28, the value in D28 will be changed multiple times in one session.

 

[EXCELGPS]

Re: Hidng colums

It's working I think, I added this to the beginning of your code in post #12 , I'll test it properly now and report back, thanks for you help on this.

Columns("E:AB").Select
    Selection.EntireColumn.Hidden = False

 

[JackDanIce]

Re: Hidng colums

I realised what the mistake was hence the code looks different.

Cells(HeaderRow, 6).Resize(, 22).EntireColumn.Hidden = False

If HeaderRow = 1 then Cells(HeaderRow, 6) = F1 (F 6th letter of alphabet)

Cells(HeaderRow, 6).Resize(,22) is F1:AA1

If you need E:AB, change that line of code to:

Cells(HeaderRow, 5).Resize(, 24).EntireColumn.Hidden = False

 

[EXCELGPS]

Re: Hidng colums

Al working perfectly now, thanks for your help.

 

[JackDanIce]

Re: Hidng colums

You're welcome, glad it's resolved