Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    VBA to hide columns (XP)

    Hi Loungers,

    I'm having trouble with the syntax needed to hide a range of columns. If the columns were known, I'd use something per the macro recorder like
    Columns("B:C").Select
    Range("B7").Activate
    Selection.EntireColumn.Hidden = True

    But the columns are not known (they can change whenever I run the macro). So the macro prompts for the set of columns such as
    first_col_hide = inputbox("enter first col",,"xxx")
    last_col_hide = inputbox("enter last col",,"xxx")

    Using the 2 variables above (which are dim'd as strings) per the syntax from the macro recorder does not work. I must be having a brain fog bcs, after trying several combinations, I can't still figure it out. I'm wondering if I need to use an offset in this case ??? <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    TIA

    Fred

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: VBA to hide columns (XP)

    Hi Hans,

    Thanks much. I couldn't think of concatenating the columns with a : No problem with the columns to be hidden not be selected - not sure why I'd want to do that anyway.

    However, my system did not support a method of ...EntireColumn.Hide. Not sure if this is in 2007 (if you have that yet). I had to do ...EntireColumn.Hidden = True.

    Fred

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to hide columns (XP)

    Edited by HansV to correct error

    You can use

    Range(first_col_hide & ":" & last_col_hide).EntireColumn.Hidden = True

    Note that this line doesn't select the range to be hidden.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to hide columns (XP)

    Sorry, the .Hide was a stupid mistake, it should indeed be .Hidden = True. I have corrected my previous reply.

  5. #5
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to hide columns (XP)

    There are a couple of other ways to do this.
    These use the Application.InputBox method rather than the InputBox function. One difference between the two is that Application.InputBox can accept ranges input via mouse.
    <code>
    Sub HideColumnsChosenWithMouse()
    Dim uiFirstColumn As Range
    Dim uiLastColumn As Range

    On Error Resume Next
    Set uiFirstColumn = Application.InputBox("Select the first column with the mouse", Type:=8)
    On Error GoTo 0
    If uiFirstColumn Is Nothing Then Exit Sub: Rem cancel pressed

    On Error Resume Next
    Set uiLastColumn = Application.InputBox("Select the last column with the mouse", Type:=8)
    On Error GoTo 0
    If uiLastColumn Is Nothing Then Exit Sub: Rem cancel pressed

    Range(uiFirstColumn, uiLastColumn).EntireColumn.Hidden = True
    End Sub</code>

    The user typing in a column address requires validation, in case they type an invalid column name, like "Apple Pie"
    <code>
    Sub HideColumnsFromTypedAddresses()
    Dim uiFirstCollAddress As Variant
    Dim uiLastCollAddress As Variant
    Dim EntryIsValidColumnAddress As Boolean

    Do
    uiFirstCollAddress = Application.InputBox("Enter the number/letter of the first hidden column.", Type:=7)
    If uiFirstCollAddress = False Then MsgBox "x": Exit Sub: Rem cancel pressed
    On Error Resume Next
    EntryIsValidColumnAddress = (TypeName(Columns(uiFirstCollAddress)) = "Range")
    On Error GoTo 0
    Loop Until EntryIsValidColumnAddress

    EntryIsValidColumnAddress = False

    Do
    uiLastCollAddress = Application.InputBox("Enter the number/letter of the last hidden column.", Type:=7)
    If uiLastCollAddress = False Then MsgBox "x": Exit Sub: Rem cancel pressed
    On Error Resume Next
    EntryIsValidColumnAddress = (TypeName(Columns(uiLastCollAddress)) = "Range")
    On Error GoTo 0
    Loop Until EntryIsValidColumnAddress

    Range(Columns(uiFirstCollAddress), Columns(uiLastCollAddress)).Hidden = True
    End Sub</code>

    Or you could have the user select the whole range of columns to be hidden rather than specify start and end.
    <code>
    Sub HideColumnsInOneSwellFoop()
    Dim uiRangeSelected

    On Error Resume Next
    Set uiRangeSelected = Application.InputBox("Select cells from all the columns you want hidden.", Type:=8)
    On Error GoTo 0
    If uiRangeSelected Is Nothing Then Exit Sub: Rem Cancel pressed

    uiRangeSelected.EntireColumn.Hidden = True
    End Sub</code>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: VBA to hide columns (XP)

    No problem, Hans.

    One more question with hidden columns.

    What I'm doing is setting up a print area to contain col A, which has a list of names, and then a set of columns for the current "period" (the period might be 2 weeks) - each day in the period has its own column.

    So as the days go into the past, I want to hide those columns so that col A and the columns for the upcoming period appear adjacent. (I know that I can set up a Print Area for non-consecutive cols but then each group of adjacent cols will print on its own page.)

    I know that I could use the inputbox to get both the first col and last col of the current period. But the first col will always be the col after the last col hidden unless this is the first time hiding columns (that is, the very first current period will start in col B and the previous col will not be hidden, since that's col A with the names). So it seems like a waste to do the prompting. Therefore what I'd like is a way to determine the first non-hidden col, allowing for that first-time usage also, and just prompt for the last col to include in the print area (since the number of cols in the "current period" might change.

    Thanks.

    Fred

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: VBA to hide columns (XP)

    Mike,

    This looks good. I'll have to give it a try when I have more time. I only resorted to inputbox since I didn't know this method. Also, since this is just for me to save time, I'm hoping I can get the col letters correct. But I did think of that problem also and decided to forego the error checking.

    Fred

  8. #8
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA to hide columns (XP)

    <code>
    With ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
    If .Areas.Count = 1 Then
    MsgBox "no columns are hidden"
    Else
    MsgBox .Areas(2).Column & " is the first visible column after the hidden columns."
    End If
    End With</code>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA to hide columns (XP)

    Try this:

    Dim lngStart As Long
    Dim lngEnd As Long
    ' Find last hidden column, stop at column B
    For lngStart = Columns.Count To 2 Step -1
    If Cells(1, lngStart).EntireColumn.Hidden = True Then Exit For
    Next lngStart
    ' One to the right
    lngStart = lngStart + 1
    ' Get last column to hide
    lngEnd = Application.InputBox(Prompt:="Select last column to be hidden", Type:=8).Column
    ' Hide range of columns
    Range(Cells(1, lngStart), Cells(1, lngEnd)).EntireColumn.Hidden = True

    You can add error handling as in Mike's examples.

Posting Permissions

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