Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    goto a column (2003)

    Hello Everyone,
    I need some help. I am attaching a file with a list on cell a2. I need to create a macro to hide the columns and to have a selection under the list box to show all. Thanks for any help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: goto a column (2003)

    This macro when run will hide the columns of the cells selected
    <pre>Sub HideColumn()
    Selection.EntireColumn.Hidden = True
    End Sub</pre>


    This will unhide all the columns in the active sheet

    <pre>Sub UnhideAllColumns()
    Cells.EntireColumn.Hidden = False
    End Sub</pre>


    Steve

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: goto a column (2003)

    To add All to the list, just make another named list and add All to it. I used =Sheet1!$B$1, etc in the list, so if you change the toppers in Sheet1, the items will change in the drop-down. I usually add this named-list to another worksheet that I keep hidden. In your case, I used Sheet2 (but didn't hide it.)

    To hide all but the selected item:
    <pre>Option Explicit

    Sub QuickHide()
    Dim i As Integer
    ActiveSheet.UsedRange.EntireColumn.Hidden = False
    If [A2] = "All" Then Exit Sub
    For i = 2 To ActiveSheet.UsedRange.Columns.Count
    If [A2] <> ActiveSheet.Cells(1, i) Then _
    Columns(i).Hidden = True
    Next i
    End Sub
    </pre>


    To run that automagically, put the following in the Sheet1 code:
    <pre>Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$2" Then QuickHide
    End Sub
    </pre>

    Merry Christmas! See attached for the whole thing. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: goto a column (2003)

    Does this do what you want?
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •