Hiding columns using VBA

Question

I am currently trying to hide certain columns if Row(8:8) meets certain criteria. My For Each loop is currently not working. Could someone point me in the right direction?

Sub hide()
' hide Macro
'
Dim rng As Range

    For Each rng In Range("F:BJ").Columns
        If Rows.Range("8") = "Test" Or Rows.Range("8") = "Test1" Then
            Column.rng.EntireColumn.Hidden = True
        End If

    Next rng
End Sub

Bond · Accepted Answer · 2015-07-31 23:21:59Z

3

You can do it this way:

Dim rng As Range
For Each rng In Range("F8:BJ8")

    If rng.Value = "Test" Or rng.Value = "Test1" Then
        rng.EntireColumn.Hidden = True
    End If

Next rng

answered Jul 31, 2015 at 23:21

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

@Bond - I realize my response is very close to yours but I will leave it be as it removes case sensitivity and toggles the column back to visible if the value has changed.

Bond Over a year ago

@Jeeped - The more the merrier, if it helps the OP.

user4039065 · Accepted Answer · 2015-07-31 23:25:44Z

2

Presumably, you would want to unhide the columns if the value in row 8 was changed programmatically or otherwise.

Dim rng As Range
With Worksheets("Sheet1")
    For Each rng In .Range("F8:BJ8")
        rng.EntireColumn.Hidden = _
            CBool(LCase(rng.Value) = "test" Or LCase(rng.Value) = "test1")
    Next rng
End With

answered Jul 31, 2015 at 23:25

user4039065

Comments

Don Jewett · Accepted Answer · 2015-07-31 23:32:37Z

1

The Rows and Columns ranges refer to the whole spreadsheet if you don't specify a range.

Sub hideColumn()

    Dim rng As Range

    For Each rng In Range("F:BJ").Columns
        If rng.Rows(8) = "Test" Or rng.Rows(8) = "Test1" Then
            rng.EntireColumn.Hidden = True
        End If
    Next rng

End Sub

answered Jul 31, 2015 at 23:32

Don Jewett

2,00715 silver badges29 bronze badges

Collectives™ on Stack Overflow

Hiding columns using VBA

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related