CODEWITHSUNDEEP
vbaexcel
Z. Turienzo
Z. Turienzo

Reputation: 15

Hiding columns from 2 ranges

I have to hide columns when a range is empty, and I have 2 ranges in different sheets to control when to hide or unhide these columns. I'm trying to use a FOR loop with 2 variables, but i's not working, this is my code:

Sub HiddenColumns()

    Dim HiddenColumn1 As Range
    Dim HiddenColumn2 As Range

    Dim c As Range
    Dim d As Range


    Set HiddenColumn1 = Range("rngColumnHidden")
    Set HiddenColumn2 = Range("rngColumnHidden2")

    For Each c In HiddenColumn1
        For Each d In HiddenColumn2

            If c.Value = "" Then
                c.EntireColumn.Hidden = True
                If d.Value = "" Then
                d.EntireColumn.Hidden = True
                End If
            End If
        Next d
    Next c

End Sub

With one range it's working perfectly, but when I try to hide another range, I have problems, this is the code for one range:

Sub HiddenColumns()

    Dim HiddenColumn1 As Range
    Dim c As Range

    Set HiddenColumn1 = Range("rngColumnHidden")
    For Each c In HiddenColumn1

            If c.Value = "" Then
                c.EntireColumn.Hidden = True
            End If
    Next c

End Sub

Upvotes: 0

Views: 135

Answers (1)

DisplayName
DisplayName

Reputation: 13386

edited after OP's comment

don't nest loops

Sub HiddenColumns()
    Dim c As Range

    For Each c In Range("rngColumnHidden").Rows(1).Cells
        c.EntireColumn.Hidden = (c.Value = "")
    Next c

    For Each c In Range("rngColumnHidden2").Rows(1).Cells
        c.EntireColumn.Hidden = (c.Value = "")
    Next c
End Sub

and for the sake of avoiding code repetitions you could use a helper sub and code

Sub HiddenColumns()
    HideColumns Range("rngColumnHidden")
    HideColumns Range("rngColumnHidden2")
End Sub

Sub HideColumns(columnsRng As Range)
    Dim c As Range
    For Each c In columnsRng.Rows(1).Cells
        c.EntireColumn.Hidden = (c.Value = "")
    Next c
End Sub

Upvotes: 1

Related Questions