Excel filtering / hiding columns using dynamic macros

Question

I have a spreadsheet that is incredibly large hundreds of columns by thousands or rows, I have the ability to check boxes to determine what columns are displayed. The problem is I now want to group the columns based on the value of a cell towards the top of each column (Row 10). This would allow the category to be dynamic and will allow an item to change from one category to another and be either hidden or displayed based on the check box state for that category.

The code that I currently have in the macro to allow the checkbox to toggle between hidden and visible per each team is below. they are currently grouped in groups of 20, I want to eliminate the static grouping of 20 and replace that with the array of columns whose cell on row 10 contains the category (Currently "Team 1" in this example.

Sub Team1()
Set target = Range("B1")
If target.Value = "TRUE" Or target.Value = "True" Then
    ActiveSheet.Columns("F:Y").EntireColumn.Hidden = False
ElseIf target.Value = "FALSE" Or target.Value = "False" Then
    ActiveSheet.Columns("F:Y").EntireColumn.Hidden = True
End If
End Sub

JNevill · Accepted Answer · 2016-03-29 15:07:45Z

1

You can use a For Loop to check each Columns Row 10 value, then set the .hidden property of the entireColumn accordingly:

Sub Team1()
    Dim target As Range
    Dim checkCell As Range

    Set target = Range("B1")

    For Each checkCell In Range("F10:Y10").Cells
        checkCell.EntireColumn.Hidden = (LCase(target.Value) = "true" And checkCell.Value = "Team 1")
    Next checkCell

End Sub

The For Each loop will loop through each cell in the range F10:Y10.

There's no need for IF... Then here since .Hidden has a true/false assignment. The (LCase(target.Value) = "true" And checkCell.Value = "Team 1") bit will either return True or False depending on those two conditions.

answered Mar 29, 2016 at 15:07

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

NotJDoe Over a year ago

That does not work properly since I have multiple different Teams, your example works for toggling one and only one on and off but will now work when combined with 2 or more teams. I may have not explained it clearly enough in the initial post but say there are 10 teams I want to be able to select for example teams 3,5,8 and view only those 3. That was the reason for the if else statements.

Collectives™ on Stack Overflow

Excel filtering / hiding columns using dynamic macros

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related