Hide columns with certain coditions

  • Thread starter Thread starter moosa Abrahams
  • Start date Start date
This should do it. Assuming of course, you mean by positive and negative =
Yes and No, Respectively. If not then see the second procedure.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If UCase(sh.Range("A5")) = "NO" Then
Columns("D:F").Hidden = True
Else
Columns("D:F").Hidden = False
End If
End Sub

If you mean numeric negative and positive.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If sh.Range("A5").Value >= 0 Then
Columns("D:F").Hidden = True
ElseIf sh.Range("A5") < 0 Then
Columns("D:F").Hidden = False
Else
MsgBox "The value is not numeric"
End If
End Sub
 
I had the symbols reversed for the numeric version. This corrects it.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If sh.Range("A5").Value < 0 Then
Columns("D:F").Hidden = True
ElseIf sh.Range("A5") >= 0 Then
Columns("D:F").Hidden = False
Else
MsgBox "The value is not numeric"
End If
End Sub
 
This will now give you the message if a non numeric value is entered in A5.

Sub hidCol()
Dim sh As Worksheet
Set sh = ActiveSheet
If Not IsNumeric(Range("A5").Value) Then
MsgBox "The value is not numeric"
ElseIf sh.Range("A5").Value < 0 Then
Columns("D:F").Hidden = True
ElseIf sh.Range("A5").Value >= 0 Then
Columns("D:F").Hidden = False
End If
End Sub

I overlooked the fact that an alpha character is considered by VBA to be a
value greater than a numeric character and therefore, greater than zero.
 
Back
Top