Hide Columns if changes to any one of a range of cells

[DWNewmac]

Hello

I'm trying to add onto some existing code, a further step that will hide columns - I think a slightly modified requirement to those I've seen when looking through the forum.

I've created a sub routine called 'HideColumns', which works when run on its own:

Sub HideColumns()

'Hide columns until they are needed
    
Dim c As Range

    For Each c In Range("AA1:AZ1").Cells
        If c.Value = "NoShow" Then
        c.EntireColumn.Hidden = True
        
        ElseIf c.Value = "Show" Then
        c.EntireColumn.Hidden = False
        
        End If
    Next c

End Sub

But I'm having difficulty in working out how to call the routine from the private sub. The code that I have so far (3rd section) is as follows and I'm wanting it to run, should there be a change to any of the cells in the range (Y9 to AZ27)

Private Sub Worksheet_Change(ByVal Target As Range)
  
 ' Selection of the shop name returns the post code and telephone number (if available)
  
  If Target.Address = "$Q$5" Then
    Application.EnableEvents = False
    Call getdata
    Application.EnableEvents = True
  
 ' Change to Y5 results in rows being hidden
  
  ElseIf Target.Address = "$Y$5" Then
    Application.EnableEvents = False
    Call HideRows
    Application.EnableEvents = True
  
  ' Change to any of the cells in the range results in the columns being hidden
  
  ElseIf Target.Address = "$Y$9:$AZ$27" Then
    Application.EnableEvents = False
    Call HideColumns
    Application.EnableEvents = True
  
End If
    
End Sub

As ever - any help would be much appreciated.

Thanks

David

 

[BobUmlas]

You're asking the routine to be called if the target is Y9:AZ27 but you want it to run if the target is IN that range, not if it IS that range. Change the code to
if Not Intersect(target,range("Y9:AZ27")) is Nothing then hidecolumns

 

[Fluff]

How about

  ElseIf Not Intersect(Target, Range("$Y$9:$AZ$27")) Is Nothing Then
    Application.EnableEvents = False
    Call HideColumns
    Application.EnableEvents = True
  
End If

What determines if row 1 is show or no show? Is it a formula?

 

[DWNewmac]

That works!! Thanks Bob and Fluff.

Yes - it's a formula in row one that determines whether or not a specific column should be shown.

Cheers
David

 

[Fluff]

What is the formula?

 

[DWNewmac]

It's a counta of the previous data input range so for example in AA1 the formula is: =IF(COUNTA(Y9:Y27)>0,"Show","NoShow")

In column AB1 is is simply = AA1. This is because there are blank columns between the data input cells (aesthetical as opposed to serving a specific purpose).

These formulas carry on through to AZ

 

[Fluff]

Ok, in that case if you are happy changing the formula to
=IF(COUNTA(Y9:Y27)>0,"Show")
You can then use this to hide the columns

Sub HideColumns()

'Hide columns until they are needed
   On Error Resume Next
   Range("AA1:AZ1").SpecialCells(xlFormulas, xlLogical).EntireColumn.Hidden = True
   On Error GoTo 0
End Sub

 

[DWNewmac]

I certainly am - thank you for that

David

 

[Fluff]

You're welcome & thanks for the feedback