Auto hide column based on multiple cell values

[slam]

Hi all,

I am looking for some VBA magic to achieve a solution to my problem. I've tried a few similar examples I've found online, but without success.

I am looking to auto hide a column (C through AH) if every row in that respective column from Row 3 to Row 42 has a - The hyphen is produced by a formula and is not typed directly in the cells. The hyphen can change to another value, via the formula, and I would then want the column to auto unhide upon worksheet activate.

Not sure if it makes a difference, but the worksheet is protected.

Thanks very much.

 

[ParamRay]

Try this:

Private Sub Worksheet_Calculate()

  Dim i As Integer
  Dim r As Range
  Dim iCounter As Integer
  
  For i = 3 To 34
    
    iCounter = 0
    For Each r In Range(Me.Cells(3, i), Me.Cells(42, i))
      If InStr(r.Value, "-") > 0 Then iCounter = iCounter + 1
    Next r
    
    If iCounter = 40 Then
      Me.Columns(i).Hidden = True
    Else
      Me.Columns(i).Hidden = False
    End If
    
  Next i

End Sub

 

[slam]

Thank you! This worked perfectly. However, I forgot I had some existing code which caused some of these same columns to auto fit. I currently have that occurring under worksheet_activate where the password is required to allow the column resizing. Is it possible to include this in your code where it will only auto fit for columns that it isn't hiding, i.e. where there is something other than a dash in every cell in that column?

Thanks

 

[ParamRay]

Add this below the Else line:

Me.Columns(i).AutoFit

 

[slam]

Try this:

Private Sub Worksheet_Calculate()

  Dim i As Integer
  Dim r As Range
  Dim iCounter As Integer
  
  For i = 3 To 34
    
    iCounter = 0
    For Each r In Range(Me.Cells(3, i), Me.Cells(42, i))
      If InStr(r.Value, "-") > 0 Then iCounter = iCounter + 1
    Next r
    
    If iCounter = 40 Then
      Me.Columns(i).Hidden = True
    Else
      Me.Columns(i).Hidden = False
    End If
    
  Next i

End Sub

Hi gpeacock,

I've encountered a problem with this, and it's my fault for not explaining something more clearly.

It should only hide the column if the ONLY value in it is "-". However, it's hiding the column any time there's a - in it. For instance "Mary-Jane" would cause the column to be hidden and I don't want that.

Is there an easy change that can be made?

Kind regards