Excel VBA Show/Hide table columns based on a specified cell value-CodePudding

I am trying to show a number of columns in a table based on a number vale in cell C11 on the same worksheet.

The data is currently in a table but if it helps, it doesn't have to be.

Cell C11 contains a number of weeks i.e. "6", range "Headers" include the week week number (this range is the header range of "Table1".

The number of columns is never a fixed amount, it is dynamic based on the value of C11.

I've tried two scripts so far but neither do anything:

1 -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rHdr As Range, rHdrs As Range
Dim strFirstAddr As String
If Target.Address <> "$C$11" Then Exit Sub
Set rHdr = Range("Headers").Find(Target.Value, LookIn:=xlFormulas)
If Not rHdr Is Nothing Then
    strFirstAddr = rHdr.Address
    Set rHdrs = rHdr
    Do
        Set rHdrs = Application.Union(rHdrs, rHdr)
        Set rHdr = Range("Headers").FindNext(rHdr)
    Loop Until rHdr.Address = strFirstAddr
    Range("Headers").EntireColumn.Hidden = True
    rHdrs.EntireColumn.Hidden = False
End If

End Sub

2 -

Sub HideColumns()
Dim p As Range

    For Each p In Range("Headers").Cells
        If p.Value < C11 Then
            p.EntireColumn.Hidden = True
        End If
    Next p
End Sub

CodePudding user response：

Can you try this and let me know if it works? I don't really understand your approach but this works for me. Just specify your controlCell and tableToHide (the table you are trying to control) at the top of the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim controlCell As Range, tableToHide As Range
    Set controlCell = Range("G1")
    Set tableToHide = Range("Table1")
    
    If Target = controlCell Then
        tableToHide.EntireColumn.Hidden = False
        For i = controlCell.Value   1 To tableToHide.Columns.Count
            tableToHide.Columns(i).EntireColumn.Hidden = True
        Next i
    End If
End Sub

CodePudding user response：

You don't need to check each column. As your data is in a table then the column headers must be unique.

I guess you could use On Error Resume Next and try to hide the column - if the column doesn't exist an error occurs and is ignored.

I prefer to avoid any errors, so search for the column first and hide it if it exists.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$11" Then
        With ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1") 'Table1 on Sheet1 in workbook containing this code.
            .Range.EntireColumn.Hidden = False 'Unhide all columns in table.
            If Not .HeaderRowRange.Find(What:=Target, _
                                        LookIn:=xlValues, _
                                        LookAt:=xlWhole, _
                                        SearchOrder:=xlByColumns, _
                                        SearchDirection:=xlNext) Is Nothing Then 'Does required column exist?
                .ListColumns(Target).Range.EntireColumn.Hidden = True 'Hide required column.
            End If
        End With
    End If

End Sub