Hide columns with certain coditions

J

JLGWhiz

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
 
J

JLGWhiz

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
 
J

JLGWhiz

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top