Hide column based on string

[smitty120]

I’m new to both VBA and this forum, so forgive me if I’ve committed any blunders. On a daily basis I receive a spreadsheet with the strings “Yes” and “No” in the cells of Column X. I would like to hide Column X if it contains any value other than Yes. What I’ve got below will run in my test spreadsheet, but it hides both the column it’s supposed to hide and the one it’s not. I’m not sure why it’s not recognizing the Yes string. Thanks for your help!

Sub Hide()
Application.ScreenUpdating = False
Dim Y, N As String
    Y = "Yes"
    N = "No"
For Each Cell In Range("X2:X1000")
    If Cell.Value = "" Or Cell.Value = N Then
        Cell.EntireColumn.Hidden = True
    ElseIf Cell.Value <> "" And Cell.Value = Y Then
        Cell.EntireColumn.Hidden = False
    End If
Next Cell
Application.ScreenUpdating = False
End Sub

 

[ukmikeb]

deleted

 

[Snakehips]

Smitty120-,

Welcome to MrExcel.

If you remove the Application.ScreenUpdating = False from your code you would see that as you loop down the rows 2 to 1000 the column would hide / unhide deendent upon the value in each row.

Thus your final result will be determned by the value in cell X1000.

If as you appear to say you want the column hidden if not all cells in the range x2:x1000 = Yes then try....

Sub Hide()
If Not Application.WorksheetFunction.CountIf(Range("X2:X1000"), "Yes") = Range("X2:X1000").Rows.Count Then
Range("X2:X1000").EntireColumn.Hidden = True
Else
Range("X2:X1000").EntireColumn.Hidden = False
End If
End Sub

Hope that helps.

 

[smitty120]

Thanks for the welcome and the response! I see now why my code isn't working. I don't think I was clear enough in my original post. I want the column to remain unhidden if any one cell equals Yes, not the cells in the range equal Yes. How would I write that code?

 

[Snakehips]

smitty120,

Try this.....

Sub Hide()
If Application.WorksheetFunction.CountIf(Range("X2:X1000"), "Yes") > 0 Then
Range("X2:X1000").EntireColumn.Hidden = True
Else
Range("X2:X1000").EntireColumn.Hidden = False
End If
End Sub

 

[Rick Rothstein]

Try this.....

Sub Hide()
If Application.WorksheetFunction.CountIf(Range("X2:X1000"), "Yes") > 0 Then
Range("X2:X1000").EntireColumn.Hidden = True
Else
Range("X2:X1000").EntireColumn.Hidden = False
End If
End Sub

Your code can be shortened to this...

Sub Hide2()
  Columns("X").Hidden = WorksheetFunction.CountIf(Range("X2:X1000"), "Yes")
End Sub

 

[Rick Rothstein]

Your code can be shortened to this...

Sub Hide2()
  Columns("X").Hidden = WorksheetFunction.CountIf(Range("X2:X1000"), "Yes")
End Sub

And if you do not mind the square bracket notation for the Evaluate function, this formula can be compacted even more...

Sub Hide()
Columns("X").Hidden = [COUNTIF(X2:X1000,"Yes")]
End Sub

 

[Snakehips]

And if you do not mind the square bracket notation for the Evaluate function, this formula can be compacted even more...

Sub Hide()
Columns("X").Hidden = [COUNTIF(X2:X1000,"Yes")]
End Sub

Rick,
Maybe if the OP wouldn't mind promising to have at least one "Yes" in X2:X100 we could just hide column X and do away with the code altogether!!

Seriously, a typically efficient solution.
I didn't fully appreciate that any expression resolving to a number greater than 0 is considered as True.

I've seen you using evaluate on many occasions but don't recall you using the square bracket shorthand. Is it something you would normally avoid? Perhaps on the grounds of readability?

 

[Rick Rothstein]

Rick,

I've seen you using evaluate on many occasions but don't recall you using the square bracket shorthand. Is it something you would normally avoid? Perhaps on the grounds of readability?

Yes, personally, I do not like the square bracket notation, one, for readability and, two, you cannot concatenate its argument together from other text and values the way you can in the Evaluate function (that argument for the square brackets is fixed, although there is a workaround using Named ranges, but I don't like that either)... however, you cannot argue that it doesn't produce really compact code when the argument can be fixed like in this case.

 

[smitty120]

OP here - it's worked! I've finally gotten it to work after being stuck for days working on it on my own. Thank you both very much for helping me out with this!