VBA to hide columns - with a slight twist ..

[David Evans]

I want to be able to hide a column and its adjacent column when the value of the left column starts with "Acct."

In the example file I'd want to hide columns E & F based on the Left 5 characters of E1 being equal to "Acct." - likewise the other columns to the right of them.

This worksheet is very wide - it can potentially have 75 accounts (150 Columns!!!) - in the interests of full disclosure I would add that I did not design it, but I have inherited it, with a caveat in the will that it cannot be changed too dramatically

 

[wudixin96]

    Dim intFirstCol As Integer
    Dim intLastCol As Integer
    Dim intLoop As Integer
    intFirstCol = 1
    intLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For intLoop = intFirstCol To intLastCol
        If Left(Cells(1, intLoop).Value, 5) = "Acct." Then
            Cells(1, intLoop).Resize(1, 2).EntireColumn.Hidden = True
            intLoop = intLoop + 1
        End If
    Next intLoop
    Application.ScreenUpdating = True

Like this ??

 

[David Evans]

    Dim intFirstCol As Integer
    Dim intLastCol As Integer
    Dim intLoop As Integer
    intFirstCol = 1
    intLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Application.ScreenUpdating = False
    For intLoop = intFirstCol To intLastCol
        If Left(Cells(1, intLoop).Value, 5) = "Acct." Then
            Cells(1, intLoop).Resize(1, 2).EntireColumn.Hidden = True
            intLoop = intLoop + 1
        End If
    Next intLoop
    Application.ScreenUpdating = True

Like this ??

Very Nice - Appreciate your efforts. I'll tweak it to set the range as I have other "stuff" to contend with beyond these accounts.

Again - many thanks