Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoFilter (Excel 2003)

    In Excel 2000 when I filtered a list using AutoFilter I could select filtered rows by clicking and dragging the row numbers. Then if I performed an action such as delete row the only rows deleted would be the visible ones. In Excel 2003 all rows whether visible or not would be deleted.
    Is there a setting in Excel 2003 to change the selection behaviour?

    Graham

  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: AutoFilter (Excel 2003)

    Hans, are you sure? This is the case with "hidden rows", but filtering is/was different than hiding...

    In XL97 (and I just tested it) if a list is filtered and you select all the rows (with the mouse either selecting the top and holding the button and dragging, or selecting the top and then shift-selecting the last row) when you delete the rows only the visible rows are deleted. The rows that have been "filtered out" are not deleted.

    My impression is that Graham found this exact behaviour in XL2000 (I can not test this), but found it is not the way of things in XL2003 (I can also not test this)...

    If I actually "Hide" the rows rather than filter them, then all rows (hidden and visible) are selected and deleted. (which seems to be what you are referring to in previous versions)

    Steve

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

    Re: AutoFilter (Excel 2003)

    Added: see Steve's reply below!

    I suspect you're mistaken. In all versions of Excel I've used, selecting rows included both visible and invisible rows.

    However, you can accomplish what you want as follows:
    - Select the filtered rows.
    - Select Edit | Go To... (or press Ctrl+G).
    - Click Special...
    - Select Visible cells only.
    - Click OK.
    You can now delete the visible rows, or otherwise manipulate them.

    If you do this frequently, you can place a button that selects visible cells only on the toolbar:
    - Select Tools | Customize...
    - Activate the Commands tab.
    - Select Edit in the list of Categories.
    - Scroll down the list of Commands until you see Select Visible Cells.
    - Drag this item to a convenient location on a toolbar.
    - Close the Customize dialog.

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

    Re: AutoFilter (Excel 2003)

    Oops - you're correct. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I was thinking of selecting ordinary ranges with hidden rows. Filtered rows act as you describe in Excel 2002 too. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

    I don't have Excel 2003, so I cannot test either.

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter (Excel 2003)

    Steve - your impression is correct - Excel 2000 - works like Excel 97.
    Hans - thanks for your work around

    Graham

  6. #6
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoFilter (Excel 2003)

    I used the select visible cells operation once and now if I select rows with some filtered out the delete operation only works on the visible cells just like in previous versions. It even persists after a restart. Weird.

    Graham.

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

    Re: AutoFilter (Excel 2003)

    Just be thankful, and pray that it will stay that way...

Posting Permissions

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