CODEWITHSUNDEEP
vbaexcel
itzmurd4
itzmurd4

Reputation: 663

Hiding columns using VBA

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

Upvotes: 2

Views: 985

Answers (3)

Don Jewett
Don Jewett

Reputation: 1977

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

Upvotes: 1

user4039065
user4039065

Reputation:

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

Upvotes: 2

Bond
Bond

Reputation: 16321

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

Upvotes: 3

Related Questions