Hide / Unhide tables based on a list chosen

[Capps]

Hi all,

I’ve got a sheet with 4 tables on it.

I have a cell in B2 with data validation set to a list on another sheet.

I can currently hide or show the tables based on either columns or rows. As one table is shown the others are hidden.

What I’m wandering is if it is possible to hide or unhide each table that allows the tables to be updated and expanded without editing the number of rows or the columns in the code every so often.

Matt

 

[Fluff]

Hi & welcome to MrExcel.
What code do you currently use?

 

[Capps]

Here’s what I did for hiding and showing rows

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
            Case Is = "PROJECT INFO":
                                    ListObjects("PROJECT_INFO").Range.Select
                                    Rows("22:244").EntireRow.Hidden = True
                                    Rows("6:29").EntireRow.Hidden = False
            Case Is = "PRODUCT INFO":
                                    Rows("6:27").EntireRow.Hidden = True
                                    Rows("28:34").EntireRow.Hidden = False
                                    Rows("35:244").EntireRow.Hidden = True
            Case Is = "SONG INFO":
                                    Rows("6:39").EntireRow.Hidden = True
                                    Rows("40:80").EntireRow.Hidden = False
                                    Rows("81:244").EntireRow.Hidden = True
            Case Is = "SONG WRITER INFO":
                                    Rows("6:121").EntireRow.Hidden = True
                                    Rows("122:138").EntireRow.Hidden = False
                                    Rows("139:244").EntireRow.Hidden = True
        End Select
End If
End Sub

And here is what I did for hiding the columns

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "PROJECT INFO": Columns("E:EG").EntireColumn.Hidden = True
                                  Columns("C:D").EntireColumn.Hidden = False
        Case Is = "PRODUCT INFO": Columns("K:EG").EntireColumn.Hidden = True
                                  Columns("F:J").EntireColumn.Hidden = False
                                  Columns("C:E").EntireColumn.Hidden = True
        Case Is = "SONG WRITER INFO":
                                  Columns("C:K").EntireColumn.Hidden = True
                                  Columns("L:AL").EntireColumn.Hidden = False
                                  Columns("AM:EG").EntireColumn.Hidden = True
        Case Is = "SONG INFO"
                                  Columns("C:AL").EntireColumn.Hidden = True
                                  Columns("AN:BU").EntireColumn.Hidden = False
                                  Columns("BU:EG").EntireColumn.Hidden = True
                        
        End Select
End If
End Sub

 

[Fluff]

Is your data actually in structured tables?
Also you can only have one change event per sheet, so is this working on two different sheets?

 

[Capps]

The data is structured in tables each to be filled in as the project builds.

 

[Fluff]

In that case what are the table names?
Also can you answer the other part of my question?

 

[Capps]

The tables names are:

“Project Info”
“Product Info”
“Song Info”
“Song Writer Info”

These are all on one sheet

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim i As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("B1")) Is Nothing Then
      With Me.ListObjects
         For i = 1 To .Count
            If Replace(.Item(i).Name, "_", " ") = Target.Value Then
               .Item(i).HeaderRowRange.Columns.Hidden = False
            Else
               .Item(i).HeaderRowRange.Columns.Hidden = True
            End If
         Next i
      End With
   End If
End Sub

 

[Capps]

That worked brilliantly. Thank you sooo much.
Matt

 

[Fluff]

You're welcome & thanks for the feedback.