Question

  • Creator
    Topic
  • #2225753

    Use VBA to hide some columns

    Locked

    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.

All Answers

  • Author
    Replies
    • #2620015

      Clarifications

      by heheman ·

      In reply to Use VBA to hide some columns

      Clarifications

    • #2619921

      Assuming you can program in VBA

      by alan williams ·

      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.

      • #2618626

        Use VBA to conditionally hide some columns

        by heheman ·

        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,

    • #2619122

      VBA Code to do this

      by alan williams ·

      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

Viewing 2 reply threads