Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Autofit Filtered Data (Excel 2000 >)

    Hi all you excel experts!

    I would like to find out if it is possible to autofit columns in a filtered range. Currently when I filter data and autofit the columns, the column width is adjusted based on all rows and the width does not relate to the filtered data. Any suggestions?
    Regards,
    Rudi

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofit Filtered Data (Excel 2000 >)

    Aaaahh. Just figured it out. I suddenly remember a beautiful little feature called Special Cells | Visible cells only!
    Now to automate this procedure!
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofit Filtered Data (Excel 2000 >)

    Hmmm..what am I missing here. It debugs on the Set statement?
    <pre>Option Explicit
    Sub AutofitFilterData()
    Dim myList As Range
    Set myList = Application.InputBox("Select a cell in the list.", , "A1", , , , 8)
    If Not myList Is Nothing Then
    myList.CurrentRegion.SpecialCells(xlCellTypeVisibl e).Columns.AutoFit
    End If
    End Sub</pre>

    Regards,
    Rudi

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

    Re: Autofit Filtered Data (Excel 2000 >)

    There should be one more comma before the 8.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofit Filtered Data (Excel 2000 >)

    Thanx Hans. The object error threw me out there. I would have spotted that if the error was clearer to the
    point of being a syntax or compiler error!

    Here is the completed version of the macro to autofit filtered columns.

    <pre>Option Explicit
    Sub AutofitFilterData()
    Dim myList As Range
    On Error Resume Next
    Set myList = Application.InputBox("Select a cell in the list.", , "A1", , , , , 8)
    On Error GoTo 0
    If Not myList Is Nothing Then
    myList.CurrentRegion.SpecialCells(xlCellTypeVisibl e).Columns.AutoFit
    End If
    End Sub
    </pre>

    Regards,
    Rudi

  6. #6
    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: Autofit Filtered Data (Excel 2000 >)

    To "avoid" this problem and help make code more readable you could use the parameter names:

    <pre>Set myList = Application.InputBox( _
    Prompt:="Select a cell in the list.", _
    Default:="A1", Type:=8)</pre>


    Steve

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofit Filtered Data (Excel 2000 >)

    You are correct Steve. Good advice! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,
    Rudi

Posting Permissions

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