Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Use VBA to hide some columns

Locked

I in fact want to filter columns like filtering rows with Auto Filter. However, I know Excel cannot filter columns. Then, I thought of using VBA to do it.

I intend to set values in A3:Z3. If the values in any of these 26 cells = 1, then hide the whole column. e.g. if A3=0, B3=1, C3=1….. I want the macro to help me to hide columns B and C, when I click on the Button assigned with the Macro. My question is how to write this VBA? Please help.

Topic

Clarifications

In reply to Use VBA to hide some columns

Clarifications

Assuming you can program in VBA

In reply to Use VBA to hide some columns

Use the Macro recorder to sort out the syntax of what you want to do, then modify the code so that it does exactly what you want.

Use VBA to conditionally hide some columns

In reply to Assuming you can program in VBA

Thanks for the feedback but could you show me the exact Macro? I am very limited in VBA and use the wizard usually. I know how to do it if it is for 1 column only. e.g. If B3=1, then hide column B. But now I need to ask the program to cover a range (say A1:Z30) and if A3=1, then hide column A, if B3=1, then hide column B, etc.

Can anyone help? I need it urgently. Thanks,

Send me an example spreadsheet

In reply to Use VBA to conditionally hide some columns

to alan.williams@talk21.com, together with version info for the Excel and I’ll work it out and send it back.

VBA Code to do this

In reply to Use VBA to hide some columns

Sub FilterColumns()
‘
‘ FilterColumns Macro
‘ Macro created 11/08/2007 by Alan
‘
Dim MyRange As Range
Dim c As Range
‘ Set Range to Filter
‘Set MyRange = ActiveSheet.Range(“a2:z2”)
Set MyRange = ActiveSheet.Range(“a2:z2”)
‘ Loop through Range
For Each c In MyRange
If c.Value = 1 Then
‘ Hide Column When Value Found
Columns(c.Column).Hidden = True
End If
Next
End Sub

[Author]

Replies