Hide Tables based on user input

dsrt16 · Mar 19th 2018

I have a setup table where users will indicate how many tables they need.

I tried this vba code

Code

Application.ScreenUpdating = False
    
        If Sheet7.Range("B63").Value = "0" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table, Service4Table, Service5Table").Hidden = False
        
        ElseIf Sheet7.Range("B63").Value = "1" Then
            Sheet1.Range("ServiceTable1").Hidden = False
            Sheet1.Range("ServiceTable2, Service3Table, Service4Table, Service5Table").Hidden = True
        
        ElseIf Sheet7.Range("B63") = "2" Then
            Sheet1.Range("ServiceTable1, Service Table2").Hidden = False
            Sheet1.Range("Service3Table, Service4Table, Service5Table").Hidden = True
    
        ElseIf Sheet7.Range("B63") = "3" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table").Hidden = False
            Sheet1.Range("Service4Table, Service5Table").Hidden = True
        
        ElseIf Sheet7.Range("B63") = "4" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table, Service4Table").Hidden = False
            Sheet1.Range("Service5Table").Hidden = True
        
        ElseIf Sheet7.Range("B63") = "5" Then
            Sheet1.Range("ServiceTable1, ServiceTable2, Service3Table, Service4Table, Service5Table").Hidden = False
    
        End If
    
  
  Application.ScreenUpdating = True
      
    
End Sub

Display More

But it seems you can't use .Hidden with a table. Any ideas?

chirayuw · Mar 20th 2018

You can only hide entire rows or columns in which the table sits, you cannot hide specific cells e.g. below code

Code

Range("Table1").CurrentRegion.EntireRow.Hidden = True
Range("Table1").CurrentRegion.EntireColumn.Hidden = True

Also there is no way to hide multiple tables unless you specify a range rather than table i.e. if Table 1 to Table 3 are from column A to E and use rows 1 to 50 then code below

Code

'This
Rows("1:50").Hidden = True
Columns("A:E").Hidden = True


'Or This
Range("A1:E50").EntireRow.Hidden = True
Range("A1:E50").EntireColumn.Hidden = True

Hide Tables based on user input

Participate now!

Share