VBA - Show or Hide Column based upon value

Simon Raine
Simon Raine used Ask the Experts™
on
Hi

When a row has a value of FALSE I want to hide a column, when a row has a value of TRUE I want to display the column.

I have written the VBA to display columns with a value of TRUE (below).

How can I expand this to have one marco to do both? i.e. show true AND hide false

Thanks

Simon



`Sub showmonths()
 
Dim a As Range

    For Each a In Range("k3:ah3").Cells
        If a.Value = True Then
            a.EntireColumn.Hidden = False
        End If
    Next a

End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
i believe it is this

Sub showmonths()
 
Dim a As Range
Dim b As Range

    For Each a In Range("k3:ah3").Cells
        If a.Value = True Then
            a.EntireColumn.Hidden = False
       
       
     
           
        End If
       
    Next a
   
      For Each b In Range("k3:ah3").Cells
        If b.Value = False Then
            b.EntireColumn.Hidden = True
       
       
     
           
        End If
       
    Next b
   
   

End Sub
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You can do so by adding an ElseIf condition like this...
Sub ShowOrHideMonths()
 
Dim a As Range

    For Each a In Range("k3:ah3")
        If a.Value = True Then
            a.EntireColumn.Hidden = False
        ElseIf a.Value = False Then
            a.EntireColumn.Hidden = True
        End If
    Next a

End Sub

Open in new window

NoahHardware Tester and Debugger

Commented:
Hi there! :)

@Subodh Tiwari (Neeraj) has given a fantastic solution.

If you are not trying to embed this code into a module and assign this to a shape or button.. I guess you are trying to make this a passive setting. In that case, you will need to change the title of @Subodh Tiwari (Neeraj)'s with the following line of code and instead of putting the code into a module, you will want to put it into the sheet you want the code to function.

Sub Worksheet_Change(ByVal Target As Range)

Open in new window


image-asset.jpeg

Author

Commented:
thanks
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial