Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How two fix bust autofilter?? (Excel 2000 SR1)

    I have a spreadsheet with about 800 lines of addresses. One of the columns is Town / City, which I used to Autofilter. While one city was selected I seem to have hit a shortcut combiation (I assume) that has now completely brooken Autofilter.
    I can't now de-select Autofilter - the menus are greyed out. The filter is definitely working, as the line numbers are not consecutive and also the blue arrow is showing beside the Autofilter heading.
    Anyone any idea how to recover my filtered towns?

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

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    Welcome to Woody's Lounge!

    Perhaps you accidentally protected the worksheet? If the first item in the Tools | Protection submenu is Unprotect Sheet, select it.

    Another possibility is that you set the "Hide All" option in the View tab of Tools | Options... If so, click Show All, then click OK.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    If that didn't work and you're only after recovery you could go to another empty sheet and place the formula
    =DeadSheet!A1
    Into cell A1 (where Deadsheet is the name of the dead sheet) and then copy that cell A1 across for as many columns as in the dead one, and down for as many rows.
    Following that, select the whole sheet, copy and paste special (values) onto itself

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    Download my Name Manager.
    Start it and check for a name called "Sheet1!FilterDatabase" (Sheet1 should resemble your sheetname).
    Delete that name if it is there.
    Try to uncheck "Data, Autofilter"
    Now select all rows in your sheet by dragging accross their numbers.
    Rightclick within the selection and choose unhide.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    Thanks for the advice, but it isn't either of those. I would have guessed I'd deleted the itmes altogether, except that the line numbers are not consecutive.

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    Jan,

    I did as you suggested, but it made no difference. The Autofilter remains greyed out. There are no hidden rows/columns.

    Peter

  7. #7
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    Hi Andrew,

    I'm having difficulty with doing what you suggested. When I type =Sheet1!A1 I get "Your formula contains an invalid external reference to a worksheet."

    Peter

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

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    Can you post a (zipped) copy of your workbook? Delete worksheets not relevant for this problem from the copy.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    If you select all cells in the sheet (control-a) and hit copy, can you paste the hidden cells then?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    No, it just pastes the visible cells. In other words, the filter is operating properly!

    Peter

  11. #11
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    HansV

    Does the file appear on the forum? The spreadsheet has a lot of personal info that I don't want to promulgate.

    Peter

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

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    If you attach a file to a post, all Loungers will be able to download and view it, so you should remove sensitive data or replace it with dummy data.

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    You can send the file to me if you like. Let me know and I'll issue my address.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    New Lounger
    Join Date
    Dec 2004
    Location
    Belfast, Antrim, Ireland, Northern
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    That would be great, thanks.

    Peter

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How two fix bust autofilter?? (Excel 2000 SR1)

    The problem is that there are two sheets selected (two tabs are white). This means that any change to either sheet will be reflected in both.
    It also means autofilter is disabled.

    Click on the tab of the third sheet and back on the first and your problem is solved.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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