Hiding a column VBA based on a condition

F

Fordy39

Im attempting to hide any coumn based on a condition.

For example should 000 exist in cells B1:F9 then I want to hide
the corresponding column say coumn C.

b c d e f

1 000
2
3
4
5
6
7
8
9


Can anyone work out the code and also whats the best way to teac
yourself VBA. At the moment Im learning but very slowly can anyon
recommend anything.

Thanks for your time.


Chri
 
M

mudraker

Here is a couple of ways


1st macro looks for only match using find command on the renge

2nd macro looks at the value of each cell in the range

Sub Macro1()
Dim i As Integer
On Error Resume Next
i = Sheets("Sheet1").Range("b1:f9").Find(What:="000", _
After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:= _
False).Column
On Error GoTo 0
If i > 0 Then
Sheets("sheet1").Columns(i).EntireColumn.Hidden = True
End If
End Sub

Sub Macro2()
Dim Rng As Range
For Each Rng In Sheets("Sheet1").Range("b1:f9")
If Rng.EntireColumn.Hidden = False Then
If Rng.Value = "000" Then
Sheets("sheet1").Columns(Rng.Column).EntireColumn.Hidden = True
End If
End If
Next
End Su
 
Top