Question Locked

Use VBA to hide some columns

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

5 replies

by alan williams

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.

by heheman

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,

by alan williams

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

by alan williams

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

This discussion is closed to new replies.