Software

Question

Gravatar
Locked

Use VBA to hide some columns

By HeHeMan ·
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.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

Assuming you can program in VBA

by alan williams In reply to Use VBA to hide some colu ...

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.

gravatar
Collapse -

Use VBA to conditionally hide some columns

by HeHeMan In reply to Assuming you can program ...

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,

gravatar
Collapse -

Send me an example spreadsheet

by alan williams In reply to Use VBA to conditionally ...

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

gravatar
Collapse -

VBA Code to do this

by alan williams In reply to Use VBA to hide some colu ...

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

Back to Software Forum
4 total posts (Page 1 of 1)  

Start or search

Related Discussions

Related Forums